Moving multiple records between worksheets

[Thom49]

I'm using the below to add data from a holding sheet(sheet2) to sheet1.The first record moves as planned. I need to run through all the records in sheet2 (not always the same number of rows) to sheet1.
I've failed miserably with every "loop" "For...Next" etc. I've tried. I've only been able to move the first record in sheet2 to sheet1
_____________________________________
Sub movedata()
erow = Sheet1.Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).row

Cells(erow, 2) = Worksheets("Sheet2").Range("b1")
Cells(erow, 3) = Worksheets("Sheet2").Range("c1")
. .
End sub
_______________________________________________

Any help would be appreciated. Thanks

 

[JLGWhiz]

Give this a try

Sub t()
Dim sh1 As Worksheet, sh2 As Worksheet, c As Range
Set sh1 = Sheets("Sheet1")
Set sh2 = Sheets("Sheet2")
    For Each c In sh2.Range("B1", sh2.Cells(Rows.Count, 2).End(xlUp))
        c.Resize(, 2).Copy sh1.Cells(Rows.Count, 2).End(xlUp)(2)
    Next
End Sub

Or

Sub t2()
With Sheets("Sheet2")
    .Range("B1", Cells(Rows.Count, 2).End(xlUp)).Resize(, 2).Copy Sheets("Sheet1").Cells(Rows.Count, 2).End(xlUp)(2)
End With
End Sub

 

[Thom49]

Thanks JLGWhiz-

Slow response is because I'm trying to get the code. It's been 10 years since I did this stuff (retired 2010)--very rusty.
It's working fine except I'm not getting all the fields in the rows from the "holding file"(sheet2). I realize that I truncated MY code in the example. There are 7 fields (b-i). Trying to adjust for that just made it worse. Clearly I don't completely understand it. Thanks

 

[JLGWhiz]

If you are copying every row in columns B:I then"

Sub t2()
With Sheets("Sheet2")
    .Range("B1", Cells(Rows.Count, 2).End(xlUp)).Resize(, 8).Copy Sheets("Sheet1").Cells(Rows.Count, 2).End(xlUp)(2)
End With
End Sub

would copy and paste the entire block of data. You do not need a loop to copy a block of contiguous cells. This assumes that no column's data is longer than column B.

 

[Thom49]

As I said, I'm very rusty. I didn't give you complete info
There can't be a column-for-column copy

Cells(erow, 2) = Worksheets("Sheet2").Range("b1")
Cells(erow, 3) = Worksheets("Sheet2").Range("c1")
Cells(erow, 7) = Worksheets("Sheet2").Range("d1")
Cells(erow, 6) = Worksheets("Sheet2").Range("f1")
Cells(erow, 8) = Worksheets("Sheet2").Range("g1")
Cells(erow, 12) = Worksheets("Sheet2").Range("k1")
Cells(erow, 11) = Worksheets("Sheet2").Range("l1")

After Cells(erow,3) the column aren't in sequential order.
I"m trying to make the sheet "readable" for other users. I'll try changing the "Sheet1" column order.

I appreciate your patience

 

[JLGWhiz]

Sub t3()
Dim sh1 As Worksheet, Sheet2 As Worksheet, lr As Long
Set sh1 = Sheets("Sheet1")
Set sh2 = Sheets("Sheet2")
lr = sh1.Cells(Rows.Count, 2).End(xlUp)(2).Row
sh2.Range("B1", sh2.Cells(Rows.Count, 2).End(xlUp)).Copy sh1.Cells(lr, 2)
sh2.Range("C1", sh2.Cells(Rows.Count, 3).End(xlUp)).Copy sh1.Cells(lr, 3)
sh2.Range("D1", sh2.Cells(Rows.Count, 4).End(xlUp)).Copy sh1.Cells(lr, 7)
sh2.Range("F1", sh2.Cells(Rows.Count, 6).End(xlUp)).Copy sh1.Cells(lr, 6)
sh2.Range("G1", sh2.Cells(Rows.Count, 7).End(xlUp)).Copy sh1.Cells(lr, 8)
sh2.Range("K1", sh2.Cells(Rows.Count, 11).End(xlUp)).Copy sh1.Cells(lr, 12)
sh2.Range("L1", sh2.Cells(Rows.Count, 12).End(xlUp)).Copy sh1.Cells(lr, 11)
End Sub

 

[Thom49]

Thanks JLGWhiz!

Excellent solution. I do appreciate your sticking with me. And the first solutions will give me something to use as exemplars, once I digest 'em.
It's almost harder to get back into this now then it was to figure stuff out years ago (talkin' late 90's/early 2000's!).
Thanks again!

 

[JLGWhiz]

You're welcome,
regards, JLG