dynamic copy and paste code

dennisli

Well-known Member
Joined
Feb 20, 2004
Messages
1,070
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
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
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?
 
Upvote 0
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
 
Upvote 0
Hi.
Try this code:
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
 
Upvote 0

Forum statistics

Threads
1,218,944
Messages
6,145,357
Members
450,611
Latest member
JodiWe

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
Back
Top