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

 

[Domenic]

Try...

[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]

    [color=darkblue]With[/color] ActiveSheet.UsedRange
        LastRow = .Rows.Count + .Rows(1).Row - 1
    [color=darkblue]End[/color] [color=darkblue]With[/color]
    
    Range(Cells(LastRow, "A"), Cells(LastRow, "K")).Copy Destination:=Range("O13")
    
[color=darkblue]End[/color] [color=darkblue]Sub[/color]
[/font]

 

[Scott Huish]

Sub TEST()
Dim x As Long
x = Range("A" & Rows.Count).End(xlUp).Row
Range("A" & x).Resize(, 11).Copy Range("O13")
End Sub

 

[errtu]

Try...

[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]

    [COLOR=darkblue]With[/COLOR] ActiveSheet.UsedRange
        LastRow = .Rows.Count + .Rows(1).Row - 1
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
    
    Range(Cells(LastRow, "A"), Cells(LastRow, "K")).Copy Destination:=Range("O13")
    
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]
[/FONT]

didn't work, nothing was copied. it only worked in this sheet that had a lot of data in it, but for one that had only 1 line or 5 didn't work

 

[Peter_SSs]

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.

Your description is a little ambiguous to me so just for clarification, do you want the data from the 11 columns

a) pasted into O13, P13, Q13, ..., Y13, or

b) all combined and pasted into the single cell O13?

 

[Domenic]

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]

 

[Nalani]

Your description is a little ambiguous to me so just for clarification, do you want the data from the 11 columns

a) pasted into O13, P13, Q13, ..., Y13, or

b) all combined and pasted into the single cell O13?

OR

Transposed to O13, O14, O15, . . . etc.



errtu,
Maybe if you try to use the Macro Recorder to do what you want, then post that code, it would be more clear as to what your outcome should be. (sometimes easier then trying to explain in words)

macro recorder and F1(Help) are your friends

 

[errtu]

alright, sorry about that. so:

    Range("A28:K28").Select
    Selection.Copy
    Range("O18").Select
    ActiveSheet.Paste
End Sub

range a28:K28 is the last line with data in this particular sheet,
i then pasted that into O18. so the data will be copied to O, P, Q, R, S...
"O" is only the starting cell, I don't want everything merged to that one cell
i'd like the macro to find that last line with data by itself and then do the same, paste it to O18

 

[Peter_SSs]

So have you tried the codes already suggested?

 

[Nalani]

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

re:

it only worked in this sheet that had a lot of data in it, but for one that had only 1 line or 5 didn't work

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?