Copy last row of sheet 1 down to amount of rows on sheet 2

Question

I am trying to copy the last row of sheet 1 which changes everytime, down the amount of rows on sheet2. I have formulas in the last row of sheet1 and would like them carried down.

Below is my code where I select the last row to copy but I cant get it to go down the amount of rows on sheet2.

I need to copy the last row (which a macro puts formulas in) on sheet1. Then I need to copy that last row down the amount of rows on sheet2. Sheet2 data is from A2 to last row of sheet2.

Sheets("2018").Activate

Dim nRow As Long, nColumn As Long

nRow = Cells(Rows.Count, "A").End(xlUp).Row

nColumn = Cells(nRow, Columns.Count).End(xlToLeft).Column

Range(Cells(nRow, "A"), Cells(nRow, nColumn)).Select

Set lastrow = Range(Cells(nRow, "A"), Cells(nRow, nColumn))

bottomrow = Sheets("Needs_Assignment").Cells(Rows.Count, "A").End(xlUp).Row

Range(lastrow).AutoFill Destination:=Range(bottomrow), Type:=xlFillDefault

Thank you

so in fact you need to find the last row so you can copy it? — Our Man in Bananas, Jul 24 '18 at 15:20
Possible duplicate of [Error in finding last used cell in VBA](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-vba) — Our Man in Bananas, Jul 24 '18 at 15:22
another [related question](https://stackoverflow.com/questions/71180/how-can-i-find-last-row-that-contains-data-in-the-excel-sheet-with-a-macro) — Our Man in Bananas, Jul 24 '18 at 15:22
I found the last row and am able to copy it my problem is getting it to copy down to the last row based on sheet 2 last row — Kevin S, Jul 24 '18 at 17:27
The related question that was posted above isn't related. My range starts at the last row of sheet 1 and not a specified range ie A2:K and bottomrow — Kevin S, Jul 24 '18 at 17:30

score 0 · Answer 1 · answered Jul 24 '18 at 15:49

The following worked for me in a recent project (copies formulas in row 1 for columns B to ZZ), so would expect a very similar pattern to work for your scenario:

     Dim wrkMyWorkBook As Workbook
     'Set wrkMyWorkBook = ' Set this to a valid value 

     Dim LR As Long
     LR = wrkMyWorkBook.Sheets("Target Sheet").Cells(Rows.Count, 1).End(xlUp).Row 
     wrkMyWorkBook.Sheets("Target Sheet").Range("B1:ZZ1").AutoFill Destination:=wrkMyWorkBook.Sheets("Target Sheet").Range("B1:ZZ" & LR)

I tried this but my issue is that my range changes every time I open the file. The formula is in the last row which changes rows. — Kevin S, Jul 24 '18 at 17:31

Copy last row of sheet 1 down to amount of rows on sheet 2

1 Answers1