Help, Macro wont run from a particular worksheet

[Joel Mills]

The following code works when I run it from any worksheet in my workbook
except one titled "Instructions". It fails on the following line of code:

Range("H3").Autofill Destination:=Range("H3:H" & cLastRow)

Does anyone have any idea why it would fail or any solutions? As previously
stated it runs from any other worksheet.

Joel

Sub Cleanup()
cLastRow = Cells(Rows.Count, "C").End(xlUp).Row - 1
'This Begins the Cleanup Process, Adds Titles, & Total Budgets
'for calculated Cummulative Percent Completes
Worksheets("Export").Activate
Range("1:1").Delete
Range("1:1").Clear
Range("B:B,D:E,J:K").Delete
With Rows("2:2")
.WrapText = True
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Range("A2").Value = ("Discipline")
Range("B2").Value = ("Week Beginning")
Range("C2").Value = ("Early Ave.")
Range("D2").Value = ("Early Cumm.")
Range("E2").Value = ("Late Ave.")
Range("F2").Value = ("Late Cumm.")
Range("D1").Formula = "=MAXA(d3:d20000)"
Range("F1").Formula = "=MAXA(f3:f20000)"
Range("H2").Value = "Week Ending"
Range("I2").Value = "Discipline"
Range("J2") = "Planned Ave." & Chr(10) & "Manpower"
Range("K2").Value = "Target Early" & Chr(10) & "% Comp."
Range("L2").Value = "Target Late" & Chr(10) & "% Comp."
'This Adds Week Ending Column
Range("H3").Formula = "=B3+6"
Range("H3").Autofill Destination:=Range("H3:H" & cLastRow)
'This Adds the Discipline Column for the Data Sheet
Range("I3") = "=A3"
Range("I3").Autofill Destination:=Range("I3:I" & cLastRow)
'This Adds the Planned Column & Propts the User for the days per
'week worked to perform the conversion from mandays to men.
Range("J1").Value = InputBox("Enter the No. Days/Week Worked" & vbCr _
& "Use No. of Days in Default P3 Calendar" & vbCr _
& "Doesn't have to be an Integer")
'This Calculates Average Manpower
Range("J3").Formula = "=AVERAGE(C3,E3)/$J$1"
Range("J3").Autofill Destination:=Range("J3:J" & cLastRow)
Range("J3:J19").NumberFormat = "#,##0.0_);(#,##0.0)"
'This Adds the Column for Early % based on Cummulative Early Values
Range("D1").Formula = "=MAXA(d3:d20000)"
Range("K3").Formula = "=d3/d$1"
Range("K3").Autofill Destination:=Range("K3:K" & cLastRow)
'This Adds the Column for Late % and calculates based on Cummulative Late
Values
Range("F1").Formula = "=MAXA(f3:f2000)"
Range("L3").Formula = "=F3/F$1"
Range("L3").Autofill Destination:=Range("L3:L" & cLastRow)
Range("K:L").NumberFormat = "0.0%"
Range("K:L").ColumnWidth = 8
Range("H:H,I:I").ColumnWidth = 11.5
Columns("J:J").ColumnWidth = 9
Worksheets("Charts").Activate
End Sub

 

[Tom Ogilvy]

Test the value of cLastRow and make sure it is 4 or greater.

 

[Don Guillett]

Could it be that col C on that worksheet does not have the desired number of
rows with data.

 

[Joel Mills]

Tom & Don

Thanks for the reply. I revised my code to declare cLastRow before using
autofill and it runs from the all worksheets now. I revised it to read
"End(xlUP).Row" instead of "End(xlUp).Row - 1 because the rows had shifted
up to the final number of rows and the proper count could be determined by
"End(xlUP).Row. The code below shows where I made the change. I'm not sure
why this makes a difference, but I suppose if its works, it works.



Range("J2") = "Planned Ave." & Chr(10) & "Manpower"
Range("K2").Value = "Target Early" & Chr(10) & "% Comp."
Range("L2").Value = "Target Late" & Chr(10) & "% Comp."
'This calculates the last row of data
cLastRow = Cells(Rows.Count, "C").End(xlUp).Row
'This Adds Week Ending Column
Range("H3").Formula = "=B3+6"
Range("H3").Autofill Destination:=Range("H3:H" & cLastRow)
'This Adds the Discipline Column for the Data Sheet