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

RJPotts

Board Regular
Joined
Apr 11, 2007
Messages
139
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
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

royUK

Well-known Member
Joined
Jul 24, 2002
Messages
1,606
Something like

Code:
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

Board Regular
Joined
Apr 11, 2007
Messages
139
Something like

Code:
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

Well-known Member
Joined
Aug 1, 2006
Messages
767
Try this

Code:
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,322
Messages
5,769,453
Members
425,548
Latest member
macjagger17

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top