Edit VBA to copy/paste cells to end

[MCosca]

Hello and thank you for reading and responding to my question.

I am writing a simple Macro to copy certain cells from one sheet and paste them into another. What I do not remember how to do is copy the varying data set, and paste it to the new sheet.

For example if I have data in A2 through F2, one time, but the next have data from A2 down to F100, and another A2 to F504 (and so on with different variables).
How do I always have the macro copy all data and paste it to the new sheet? VBA below

Range("A2:F2").Select
Selection.Copy
Sheets("Automated Data").Select
Range("A2").Select
ActiveSheet.Paste
Range("G2").Select
Sheets("Summarized-data").Select
Range("J2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Automated Data").Select
ActiveSheet.Paste
Sheets("Summarized-data").Select
ActiveWindow.SmallScroll ToRight:=1
Range("N2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Automated Data").Select
Range("H2").Select
ActiveSheet.Paste
End Sub

 

[Joe4]

You can find the last row with data by picking some column that will always have data in it (for every row with data).
Let's say it is row "F". Then use this code to find the last row in column F with data:

Dim lr as Long
lr = Cells(Rows.Count, "F").End(xlUp).Row

Then you can copy from A2 down to the last row with data in column F like this:

Range("A2:F" & lr).Copy

 

[MCosca]

thank you @Joe4 - I'm not following where that command would fit into the sample I provided? Probably because Im a novice or didnt ask my question properly.

I need to identify the data from A2: to the end of F and copy it (from Summarized Data tab to Automated Data tab). This will be pasted from A2 (on the Summarized tab) into A2 through F (end) on the Automated Data tab.

Also need to same from J2 & N2 (Summarized Data tab) to end of column J & N and copy/paste it into column G & H (Automated data tab).

Im lost as to where what you indicate would fit into the below

Range("A2:F2").Select
Selection.Copy
Sheets("Automated Data").Select
Range("A2").Select
ActiveSheet.Paste
Range("G2").Select
Sheets("Summarized-data").Select
Range("J2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Automated Data").Select
ActiveSheet.Paste
Sheets("Summarized-data").Select
ActiveWindow.SmallScroll ToRight:=1
Range("N2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Automated Data").Select
Range("H2").Select
ActiveSheet.Paste
End Sub

 

[Joe4]

Your entire code can be replaced with this:

Dim lr As Long
Sheets("Summarized-data").Activate
lr = Cells(Rows.Count, "F").End(xlUp).Row
Range("A2:F" & lr).Copy Sheets("Automated Data").Range("A2")
Range("J2:J" & lr).Copy Sheets("Automated Data").Range("G2")
Range("N2:N" & lr).Copy Sheets("Automated Data").Range("H2")