Autofilling another sheet by running a macro on a different sheet

[fnpb93]

Hi all!
Thanks for the help in advance.

Sub GenerateInterval()
    Dim lastRow As Variant
    lastRow = Range("I5").Value
    With Worksheets("Backend")
        .Range("B4").Formula = "=Backend!B3+1"
        .Range("B4").AutoFill Range("B4:B" & lastRow)
    End With
End Sub

Let's say I have 2 sheets, Sheet1 and Backend
So I want to run this macro from Sheet1, and Sheet1 Cell I5 contains a value, which is the number of rows I want to autofill in the backend sheet.
Hence, from my code, after getting the value of I5 and putting it into a variable lastRow, I then activate/moved to the Backend sheet, and want to fill B4 and the rows below subsequently until it fills up to Row (I5) where I5 is an integer where the formula is shown.

Screenshots as follows:

However, it doesn't seem to work and gave me an error of Autofill method of Range Class failed. Is it because I didn't actually run the autofill formulas in the Backend spreadsheet? How do I do so?

Thanks!

May I know what I did wrongly?

 

[StephenCrump]

'Try
Sub GenerateInterval()
    
    Dim lastRow As Long
    
    lastRow = Worksheets("Sheet1").Range("I5").Value
    Worksheets("Backend").Range("B4:B" & lastRow).Formula = "=Backend!B3+1"

End Sub
'or using SEQUENCE function:
Worksheets("Backend").Range("B4").Formula2 = "=SEQUENCE(Sheet1!I5-ROW()+1)"

 

[fnpb93]

'Try
Sub GenerateInterval()
   
    Dim lastRow As Long
   
    lastRow = Worksheets("Sheet1").Range("I5").Value
    Worksheets("Backend").Range("B4:B" & lastRow).Formula = "=Backend!B3+1"

End Sub
'or using SEQUENCE function:
Worksheets("Backend").Range("B4").Formula2 = "=SEQUENCE(Sheet1!I5-ROW()+1)"

Hmm it does actually work. Why does my original code not work though? Any brief explanations for it? Thanks for the help once again

 

[StephenCrump]

Yes, sorry, I side-stepped the question by using my preferred approach rather than .Autofill.

.Range("B4").AutoFill .Range("B4:B" & lastRow)

In case it's hard to spot, I've changed the second Range to .Range

Do you see why it wasn't working / will now work? it's a common trap - omitting to fully qualify the relevant objects.