Copy data to the last row and paste below it

[vostroxe]

Hi guys, i am very new to VBA. I have tried multiple codes from this forum and still can't get it to work. Appreciate if you guys can help me on the scenario.

Scenario Example:
Column N will be determine the last row e.g cell N10.
Copy range will be from A1 until Z10 (based on last row of column N)
Paste to A11

 

[vostroxe]

Anytime when required. Maybe new Sub is required.

 

[Fluff]

What happens if you run the macro twice.
Would you want to remove all the copied data or just from the last time it was run?

 

[vostroxe]

to remove all the copied data only. Based on the scenario, the copied data will be in the cell A11 to Z20.

 

[Fluff]

You will need to store the value of the Lr variable somewhere on the sheet and then reference that to remove the copied area.

Sub Macro1()

   Dim Lr As Long
   Lr = Range("N" & Rows.Count).End(xlUp).Row
   Range("[COLOR=#ff0000]AB1[/COLOR]").Value = Lr
   Range("AA1:AA" & Lr).Formula = "=VLOOKUP(N1,'Sheet1'!A:B,2,0)"
   Range("AA1:AA" & Lr).Copy
   Range("N" & Lr + 1).PasteSpecial xlValues
   Range("AA1:AA" & Lr).ClearContents
   Range("O1:Z" & Lr).Copy Range("O" & Lr + 1)
   Range("A1:M" & Lr).Copy Range("A" & Lr + 1)
   
End Sub
Sub RemoveData()
   Range("A" & Range("[COLOR=#ff0000]AB1[/COLOR]").Value, Range("N" & Rows.Count).End(xlUp).Offset(, 12)).ClearContents
End Sub

Change the values in red to the cell where you want to store the variable

 

[vostroxe]

thanks again. never ceases to amaze me.

 

[Fluff]

You're welcome