| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help, Macro wont run from a particular worksheet
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Help, Macro wont run from a particular worksheet
Test the value of cLastRow and make sure it is 4 or greater.
-- Regards, Tom Ogilvy "Joel Mills" wrote in message ... 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Help, Macro wont run from a particular worksheet
|
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Help, Macro wont run from a particular worksheet
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 "Tom Ogilvy" wrote in message ... Test the value of cLastRow and make sure it is 4 or greater. -- Regards, Tom Ogilvy "Joel Mills" wrote in message ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Macro security prompt wont go away | Setting up and Configuration of Excel | |||
| Keep getting error, wont run macro! HELP! | Excel Discussion (Misc queries) | |||
| Date format from excel to CVS file wont. Change in CVS wont stay. | Excel Worksheet Functions | |||
| Macro wont run! | Excel Discussion (Misc queries) | |||
| Macro that wont go away - Help | Excel Worksheet Functions | |||
Linear Mode
