Copy last 20 rows (or all if less than 20)

[ronlh1]

Hello all,

I have a problem I'm sure someone could help me with.
In Excel 2010, I need to Copy last 20 rows (or all if less than 20) of columns "C" and "N" from Sheet2 to Sheet3 starting on A2I have attached a copy of the spreadsheet.

Thank you in advance.

[jolivanes]

Try this


Sub Maybe()
Dim lr As Long, fr As Long
lr = Cells(Rows.Count, 1).End(xlUp).Row
fr = IIf(lr <= 21, 2, lr - 19)
Range("C" & fr & ":C" & lr & ",N" & fr & ":N" & lr).Copy Sheets("Sheet3").Range("A2")
End Sub

[snb]

Sub M_snb()
With Sheet2.UsedRange
Union(.Columns(3), .Columns(14)).Offset(.Rows.Count - Application.Min(22, .Rows.Count - 1)).Copy Sheet3.Cells(2, 1)
End With
End Sub

[ronlh1]

Thanks for the help.
I have it working but, I had to add select sheet2 as I will be on sheet 1 when it is run.
It seems to be working as needed.

Thanks again!!

Sub Maybe()
Dim lr As Long, fr As Long
Sheets("Sheet2").Select
lr = Cells(Rows.Count, 1).End(xlUp).Row
fr = IIf(lr <= 21, 2, lr - 19)
Range("C" & fr & ":C" & lr & ",N" & fr & ":N" & lr).Copy Sheets("Sheet3").Range("A2")
Sheets("Sheet1").Select
End Sub

[jolivanes]

No, don't
Leave the code from Post #2 as is, just change the "Sheets("Sheet3")" in the 5th line to "Sheets("Sheet2")"
Running it from any Sheet that has the data to be copied in it will paste it in Sheet2



Sub Maybe()
Dim lr As Long, fr As Long
Dim sh1 As Worksheet, sh2 As Worksheet
Set sh1 = Worksheets("Sheet1") '<---- Name of the Sheet where you copy FROM. Change if required
Set sh2 = Worksheets("Sheet2") '<---- Name of the Sheet where you copy INTO. Change if required
lr = sh1.Cells(Rows.Count, 1).End(xlUp).Row
fr = IIf(lr <= 21, 2, lr - 19)
sh1.Range("C" & fr & ":C" & lr & ",N" & fr & ":N" & lr).Copy sh2.Range("A2")
End Sub
You can run this code from any sheet that is active (showing on your desktop).
It will always copy from a Sheet named "Sheet1" into a Sheet named "Sheet2"