find last used cell in columns

[errtu]

i need a macro to find the last line used (with data) in a range and paste the data of the whole line to cell O18.

Say I have range A13:K37 with data. So I need data from: A37, B37, C37, D37, E37, F37, G37, H37, I37, J37, K37 pasted to O13.

But the above is only an example, as it always changes it could be line 37 or it could be 15 or 20

 

[errtu]

I should have probably used HotPepper's method of determining the last row...

[FONT=Verdana][COLOR=darkblue]Option[/COLOR] [COLOR=darkblue]Explicit[/COLOR]

[COLOR=darkblue]Sub[/COLOR] test()

    [COLOR=darkblue]Dim[/COLOR] LastRow [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]

    LastRow = Cells(Rows.Count, "A").End(xlUp).Row
    
    Range(Cells(LastRow, "A"), Cells(LastRow, "K")).Copy Destination:=Range("O13")
    
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]
[/FONT]

this one seems to be working, thank you very much everyone

 

[Nalani]

Take a look at my post #10 on the previous page.

 

[errtu]

Are you wanting to do this on more than one sheet?

re:

Code provided by Domenic and Hotpepper was only for the active sheet.

Try this for All Sheets:

Sub test2()
    Dim ws As Worksheet
   Dim x As Long
For Each ws In ActiveWorkbook.Worksheets
    x = ws.Range("A" & Rows.Count).End(xlUp).Row
    ws.Range("A" & x).Resize(, 11).Copy ws.Range("O18")
 Next ws
 
End Sub

Every time you run it, it WILL erease what was in Row 18 Col O-Y. Is that what you wanted?

worked like a charm, i didnt see this one. its better all in one click than one by one. thank you my man!!

 

[Nalani]

Thanks for the feed back.

Glad you found what you needed.