Macro/VBA Paste Data Below the Last Used Row

[liam_lost54]

Hi Everyone,

I have the following Macro created for only one tab to select and drag all the information from specific cells to the next two available cells but I don't know how to make it go to those cells instead of just going to the same cells. Could you please assist me on this? I was looking for other posts but I couldn't figure it out.

Sub Macro3()
'
' Macro3 Macro
'

'
    Range("A3507:I3530").Select
    Selection.AutoFill Destination:=Range("A3507:I3554"), Type:=xlFillDefault
    Range("A3507:I3554").Select
    ActiveWindow.SmallScroll Down:=-9
    Range("J3531").Select
End Sub

 

[RobP]

Hi , welcome to the forum.

does this get you what you need ?

It will cut your selection, and paste it at the next row after all your data.

(keep in mind it checks column A for the last row of data - so if you have data in col F for example which is on a later Row, you should use that column for the lastrow definition. ColA = 1, ColB = 2 etc.. so change the (Rows.Count,1) to your desired col. number (F = 6).

This also CUTS the data (as you said you were dragging it). If you want to just put a copy of it at the bottom of your data instead, use "Copy" instead of "Cut"

Its not clear to me what you mean by "the next 2 available cells". You are selecting 23 rows of data (which clearly won't fit inside the next 2 available rows..), so apologies if I've misunderstood the question. I guess many others too, as I don't see any responses to you after a month ..

Sub Macro3()
'
' Macro3 Macro
'
    Dim lastrrow As Long
    
    lastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row 'find last row of data.
    Range("A3507:I3530").Cut Range("A" & lastrow + 1)
    
    
End Sub