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

 

[nemmi69]

Not sure I understand. You are looking at N10 though your range goes to Z10? Is this because N10 can be guaranteed to have data in it?

 

[vostroxe]

Not sure I understand. You are looking at N10 though your range goes to Z10? Is this because N10 can be guaranteed to have data in it?

Column N guaranteed to have data and will be a determination of the last row of the information. The required information to be copy need to be from A1 to Z10.

 

[Fluff]

Hi & welcome to MrExcel
How about

Sub CopyData()
   Dim Lr As Long
   Lr = Range("N" & Rows.Count).End(xlUp).Row
   Range("A1:Z" & Lr).Copy Range("A" & Lr + 1)
End Sub

 

[nemmi69]

Dim LastRowNo As Long


Sub CopyDownData()
LastRowNo = Worksheets("Sheet1").Cells(Rows.Count, "N").End(xlUp).Row
 
Range("A1:Z" & LastRowNo).Copy
Range("A" & LastRowNo + 1).PasteSpecial Paste:=xlPasteValues
 
End Sub

 

[Fluff]

@nemmi69
If "Sheet1" is not the active sheet, then your code will be calculate the lastrow on a different sheet from the copy/paste.

 

[vostroxe]

wow. it works perfectly. Thank you very much to both of you.

 

[Fluff]

Glad we could help & thanks for the feedback

 

[vostroxe]

Sub Macro1()

   Dim Lr As Long
   Lr = Range("N" & Rows.Count).End(xlUp).Row
   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

Got further question on this. How do I clear contents on the pasted value area only in case I have amendment on the data. A1 to Z10 to be remain on the Excel.

 

[Fluff]

Would you be looking to clear the contents soon after running the macro, or could it be the next day?