Copy 6 lines of data from 1 sheet to bottom of another

[RJPotts]

Hi,

I know I've seen here how to do this but I just cant find the same messages again. So many apologies for duplicating a question.

I have a form which transfers some manually entered data to a sheet where a few simple calculations are done. There may be upto 6 lines per batch of data, sometimes less.

The data is in 18 columns with headings such as Date / From / To etc.

I need a way of each batch of data (in sheet "Temp") being transferred to the bottom of the data (same columns headings etc) in sheet "Data". I.e. "Temp" cell A2 to the first empty row in "Data" column A, B2 to the first empty row in "Data" column B, etc. A3, B3, etc if there's a second line, A4, B4, etc if there's a 3rd line and so-on.

Could anyone please let me know how to do that or point me in the direction of a message where I can find the info.

Rgds & many thanks in advance
Richard

 

[royUK]

Something like

Dim wsSource As Worksheet
    Dim wsData As Worksheet
    Dim rToCopy As Range
    Dim rNextCl As Range

    Set wsSource = Worksheets("Temp")
    Set wsData = Worksheets("Data")

    Set rNextCl = wsData.Cells(Rows.Count, 2).End(xlUp).Offset(1, 0)
    With wsSource
        'assumes data is in Columns 1 to 18, starting Row1
        'adjust to suit
        Set rToCopy = .Range(.Cells(1, 1), .Cells(.Rows.Count, 18).End(xlUp))
        'optional
    End With
    rToCopy.ClearContents
    rToCopy.Copy rNextCl

    Set wsSource = Nothing
    Set sdata = Nothing
    Set rNextCl = Nothing
    Set rToCopy = Nothing

 

[RJPotts]

Something like

Dim wsSource As Worksheet
    Dim wsData As Worksheet
    Dim rToCopy As Range
    Dim rNextCl As Range

    Set wsSource = Worksheets("Temp")
    Set wsData = Worksheets("Data")

    Set rNextCl = wsData.Cells(Rows.Count, 2).End(xlUp).Offset(1, 0)
    With wsSource
        'assumes data is in Columns 1 to 18, starting Row1
        'adjust to suit
        Set rToCopy = .Range(.Cells(1, 1), .Cells(.Rows.Count, 18).End(xlUp))
        'optional
    End With
    rToCopy.ClearContents
    rToCopy.Copy rNextCl

    Set wsSource = Nothing
    Set sdata = Nothing
    Set rNextCl = Nothing
    Set rToCopy = Nothing

Hi Roy,

Apologies for the delay in replying but whilst moving apartment I havnt had reliable internet access.

I've just tried the above but find that I've got a few problems....... When executing it the headers (in row1, columns A-R, sheet Temp) disappear, along with the data underneath them. That data doesn't seem to get copied to the Data sheet though.

Any ideas where I might be going wrong please?

Rgds & many thanks in advance
Richard

 

[facethegod]

Try this

Public Sub ffff()
On Error Resume Next
Dim Lrow, x, s, i As Integer
x = 1
Lrow = Worksheets("Temp").Range("A65536").End(xlUp).Row
    For i = 2 To Lrow
        s = Worksheets("Temp").Range("A" & i).End(xlToRight).Column
            While Worksheets("Data").Cells(x, 1) <> ""
                    x = x + 1
            Wend
            Worksheets("Temp").Range("A" & i).Resize(, s).Copy Destination:=Worksheets("Data").Cells(x, 1)
    Next
End Sub