(Solved) – VBA – Last Row Of Different Sheets & Duplicate Copy Paste

(solved)-–-vba-–-last-row-of-different-sheets-&-duplicate-copy-paste

I know the “Last Row” question has already come up several times but even when looking at existing threads I cannot find what is happening. It is the first time I write a Macro so I have only been able to get to a certain point I paste the code and ask the questions later:

Option Explicit

Sub Practice()

'Last Row Searcher

Dim Sht As Worksheet
Set Sht = ActiveSheet
Dim Last_Row As Long
With Sht
    Last_Row = .Range("A9999").End(xlUp).Row
End With

'Column A to D
Sheet9.Select
Range("A2:A" & Last_Row).Copy
Sheet11.Select
Range("D" & Last_Row).Select
ActiveSheet.Paste

'Column C to F
Sheet9.Select
Range("C2:C" & Last_Row).Copy
Sheet11.Select
Range("F" & Last_Row   1).Select
ActiveSheet.Paste

'Column E to G
Sheet9.Select
Range("E2:E" & Last_Row).Copy
Sheet11.Select
Range("G" & Last_Row   1).Select
ActiveSheet.Paste

'Column I to L
Sheet9.Select
Range("I2:I" & Last_Row).Copy
Sheet11.Select
Range("L" & Last_Row   1).Select
ActiveSheet.Paste

End Sub

Question 1:

When I paste what I have copied to the other worksheet it directly pastes things in the “Last_Row” from the previous worksheet instead of looking for the new “Last_Row” of the Active Sheet. Is there a way around this?

Question 2

I repeat the same code several times but with different columns, because they are not together I copy column A to D, then C to F, etc.

It is working for me, but out of curiosity, is there a way to do it all at once?

Thank you!

Leave a Reply

Your email address will not be published. Required fields are marked *