Copy data to the last row and paste below it

vostroxe

New Member
Joined
Jul 13, 2018
Messages
29
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
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
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?
 
Upvote 0
to remove all the copied data only. Based on the scenario, the copied data will be in the cell A11 to Z20.
 
Upvote 0
You will need to store the value of the Lr variable somewhere on the sheet and then reference that to remove the copied area.
Code:
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
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,954
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top