LastRow Code Debug + Copy Formula in last row

[khlau]

Hello, wondering if someone can help me.

I have put together the below code but not sure why it isn't working. I am trying to copy my formulas in range B8:F8, to all the way down to the last row - this last row number is calculated from the data in another sheet.

So e.g. in the School Payment Request sheet there are 81 rows from the start of the data "C12". So I would want the formula in the sheet to copy to range B89:F89 etc.

Just a bit stumped, any help appreciated thanks!

Sub CommandButton1_Click()


Dim mylastRow As Long
mylastRow = Worksheets("School Payment Request").Range("C12" & Rows.Count).End(xlDown).Row
Range("B8:F8").AutoFill Destination:=Range("B9:F" & mylastRow), Type:=xlFillDefault
MsgBox "Imported"


End Sub

 

[wideboydixon]

This isn't going to work:

mylastRow = Worksheets("School Payment Request").Range("C12" & Rows.Count).End(xlDown).Row

For example, if Rows.Count = 1048576 (as it is in my version of Excel) you have:

mylastRow = Worksheets("School Payment Request").Range("C121048576").End(xlDown).Row

You may well get an error on the above. Using your method, you should just remove the "& Rows.Count" piece to leave:

mylastRow = Worksheets("School Payment Request").Range("C12").End(xlDown).Row

WBD

 

[khlau]

Thanks for getting back to me, WBD, have amended as suggested. However I still get a debug error stating that "Autofill method of Range class failed".

 

[wideboydixon]

Ah. You also need to change the target range to include the original selection I think:

Range("B8:F8").AutoFill Destination:=Range("B8:F" & mylastRow), Type:=xlFillDefault

WBD

 

[khlau]

doh! Thank you, literally been driving me crazy for last hour and a half!

Ah. You also need to change the target range to include the original selection I think:

Range("B8:F8").AutoFill Destination:=Range("B8:F" & mylastRow), Type:=xlFillDefault

WBD