Autofill down according to adjacent column

[HJA14]

Can someone please explain why my code doesn't work?
I want the VBA module to automatically fill certain cells (F,S,..,CS) and their corresponding formulas depending on whether cell in the column left of them is blank or not.

Sub test()

For Each sh In ActiveWorkbook.Sheets
If sh.Name <> "Overview" Then
Dim lastRowC1 As Long
Dim lastRowC2 As Long
Dim lastRowC3 As Long
Dim lastRowC4 As Long
Dim lastRowP1 As Long
Dim lastRowP2 As Long
Dim lastRowP3 As Long
Dim lastRowP4 As Long
    lastRowC1 = Range("E" & Rows.Count).End(xlUp).Row
    Range("D2").AutoFill Destination:=Range("D2:D" & lastRowC1)
    lastRowC2 = Range("R" & Rows.Count).End(xlUp).Row
[B]    Range("S3").AutoFill Destination:=Range("S3:S" & lastRowC2)[/B]
    lastRowC3 = Range("AE" & Rows.Count).End(xlUp).Row
    Range("AF3").AutoFill Destination:=Range("AF3:AF" & lastRowC3)
    lastRowC4 = Range("AR" & Rows.Count).End(xlUp).Row
    Range("S3").AutoFill Destination:=Range("AS3:AS" & lastRowC4)
    lastRowP1 = Range("BE" & Rows.Count).End(xlUp).Row
    Range("BF3").AutoFill Destination:=Range("BF3:BF" & lastRowP1)
    lastRowP2 = Range("BR" & Rows.Count).End(xlUp).Row
    Range("BS3").AutoFill Destination:=Range("BS3:BS" & lastRowP2)
    lastRowP3 = Range("CE" & Rows.Count).End(xlUp).Row
    Range("CF3").AutoFill Destination:=Range("CF3:CF" & lastRowP3)
    lastRowP4 = Range("CR" & Rows.Count).End(xlUp).Row
    Range("CS3").AutoFill Destination:=Range("CS3:CS" & lastRowP4)
    End If
    Next sh
    
End Sub

The bold line of code is an error according to Excel. Anyone any idea why this line generates an error
(and the line two rows earlier doesn't?)

 

[JLGWhiz]

the line you had embolened did not fail when I ran the code but this line did

Range("S3").AutoFill Destination:=Range("AS3:AS" & lastRowC4)

and the reason it did is because cell S3 is not in the auto fill range. I believe you have a typo in your code and the S3 should be AS3. Try

Range("[COLOR=#B22222]A[/COLOR]S3").AutoFill Destination:=Range("AS3:AS" & lastRowC4)

and see if it then works.