Copy from 1 sheet to another at the end of the last row filled

[madddogcom]

Trying to copy data from one sheet to another via VBA. I can get the first set of data to copy over. But when I try to do it again with other data the receiving sheet does not paste at say row 103, but row 2103! I've tried different things and none have worked.

After getting this to work right, I'll be combining 4-6 sheets similarly copying the data over then a large sort.

Where, oh where have I gone wrong?

Sub CopyRowsToEnd()

'Declare variables
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim rng1 As Range
Dim rng2 As Range
Dim lastRow1 As Long
Dim lastRow2 As Long


lastRow1 = Sheets("PFIT").Cells.SpecialCells(xlCellTypeLastCell).Row
    'Range("A2").Select
'Set variables to the worksheets
Set ws1 = Sheets("PFIT")
Set ws2 = Sheets("Sheet8")

'Set range to the rows to be copied
Set rng1 = ws1.Range("A2:T2" & lastRow1)

lastRow2 = Sheets("Sheet8").Cells.SpecialCells(xlCellTypeLastCell).Row
'Set range to the destination in the second sheet
Set rng2 = ws2.Range("A" & lastRow2 + 1)

'Copy the rows
rng1.copy

'Paste the rows at the end of the destination range
rng2.PasteSpecial xlPasteFormulas

End Sub

 

[AlphaFrog]

Two problems:

Set rng1 = ws1.Range("A2:T2" & lastRow1)
Say the last row was 103. Because you have T2 in the line of code above, the result range is "A2:T2103"
Omit the 2 after T2
Set rng1 = ws1.Range("A2:T" & lastRow1)



.SpecialCells(xlCellTypeLastCell).Row

This method to determine the last row is not always reliable. Example: If you were to have say 100 rows of data, then you delete the bottom 50, this method still returns 100 as the last used row until you resave the workbook.

Try this method...
lastRow1 = Sheets("PFIT").Range("T" & Rows.Count).End(xlUp).Row

Try this...

Sub CopyRowsToEnd()

'Declare variables
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim rng1 As Range
Dim rng2 As Range
Dim lastRow1 As Long
Dim lastRow2 As Long


lastRow1 = Sheets("PFIT").Range("T" & Rows.Count).End(xlUp).Row
    'Range("A2").Select
'Set variables to the worksheets
Set ws1 = Sheets("PFIT")
Set ws2 = Sheets("Sheet8")

'Set range to the rows to be copied
Set rng1 = ws1.Range("A2:T" & lastRow1)

lastRow2 = Sheets("Sheet8").Range("A" & Rows.Count).End(xlUp).Row
'Set range to the destination in the second sheet
Set rng2 = ws2.Range("A" & lastRow2 + 1)

'Copy the rows
rng1.Copy

'Paste the rows at the end of the destination range
rng2.PasteSpecial xlPasteFormulas

End Sub

Or better yet...

Sub CopyRowsToEnd()

'Declare variables
Dim ws1 As Worksheet
Dim ws2 As Worksheet

'Set variables to the worksheets
Set ws1 = Sheets("PFIT")
Set ws2 = Sheets("Sheet8")

'Copy
ws1.Range("A2", ws1.Range("T" & Rows.Count).End(xlUp)).Copy

'Paste
ws2.Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteFormulas

Application.CutCopyMode = False

End Sub

 

[madddogcom]

Thanks AlphaFrog!!

This ended up working perfectly except my date information changed from 4/20/2023 to 44985 after the copy. I figured that out though.

Sub CopyRowsToEnd()

'Declare variables
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim rng1 As Range
Dim rng2 As Range
Dim lastRow1 As Long
Dim lastRow2 As Long

'Get last row
lastRow1 = Sheets("CAR'S").Cells.SpecialCells(xlCellTypeLastCell).Row

'Set variables to the worksheets
Set ws1 = Sheets("CAR'S")
Set ws2 = Sheets("Sheet8")

'Set range to the rows to be copied
Set rng1 = ws1.Range("A2:T" & lastRow1)

'Get last row
lastRow2 = Sheets("Sheet8").Cells.SpecialCells(xlCellTypeLastCell).Row

'Set range to the destination in the second sheet
Set rng2 = ws2.Range("A" & lastRow2 + 1)

'Copy the rows
rng1.copy

'Paste the rows at the end of the destination range
rng2.PasteSpecial xlPasteFormulas

End Sub