dynamic copy and paste code

[dennisli]

Good morning,
I tried to copy Sheet1 from row 2 to the second last used row and then paste to Sheet 3 from row 7.
Then copy Sheet 2 from row 2 to the second last used row and then paste to Sheet 3 just after the row which contains the second last used row of Sheet1 data.
The second last rows in sheet1 and sheet 2 are volatile.
Any ideas will be highly appreciated.
Dennis

 

[Harvey]

second last used row =
second last row in sheet
OR IS IT
second last row that a user edited?

Do you want to copy them once, use a macro or use a formula?

 

[dennisli]

Thanks,
Sorry for confusing you.
I want to use Macro to do it.
Here, the second last used row is just the following code results:
LastRow = ActiveSheet.UsedRange.Row + ActiveSheet.UsedRange.Rows.Count

Thanks lot
Dennis

 

[Harvey]

Hi.
Try this code:

Sub copysheets()

    Dim currow_copy As Integer
    Dim currow_paste As Integer
    
    Dim biggestrow1 As Integer
    Dim biggestrow2 As Integer
    
    If Not IsNull(Sheets("Sheet1").UsedRange) Then
        biggestrow1 = Sheets("Sheet1").UsedRange.Row + Sheets("Sheet1").UsedRange.Rows.Count - 1
    Else
        biggestrow1 = 65536
    End If
    
    If Not IsNull(Sheets("Sheet2").UsedRange) Then
        biggestrow2 = Sheets("Sheet2").UsedRange.Row + Sheets("Sheet2").UsedRange.Rows.Count - 1
    Else
        biggestrow2 = 65536
    End If
    
    currow_copy = 2
    currow_paste = 7
    
    Do While currow_copy < biggestrow1
        Sheets("Sheet1").Rows(currow_copy).EntireRow.copy
        Sheets("Sheet3").Paste (Sheets("Sheet3").Rows(currow_paste).EntireRow)
        currow_copy = currow_copy + 1
        currow_paste = currow_paste + 1
    Loop

    currow_copy = 2
    
    Do While currow_copy < biggestrow2
        Sheets("Sheet2").Rows(currow_copy).copy
        Sheets("Sheet3").Paste (Sheets("Sheet3").Rows(currow_paste).EntireRow)
        currow_copy = currow_copy + 1
        currow_paste = currow_paste + 1
    Loop

End Sub

 

[dennisli]

brilliant. have a good day.