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.
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?)
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.
Code:
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?)
Last edited: