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

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

Harvey

Well-known Member
Joined
Nov 18, 2004
Messages
953
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

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

Well-known Member
Joined
Nov 18, 2004
Messages
953
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
 

Forum statistics

Threads
1,136,307
Messages
5,674,986
Members
419,541
Latest member
freddyboots

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