| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel not showing steps as I F8 through each line of VBA code
I recently switched from Excel 2003 to 2007. A macro I had would vlookup
information for a particular store into a template sheet, copy that sheet, paste it as values, and move to the next store. This macro isn't working anymore. I get an error 440 and it closes Excel for me. So I tried going showing both Excel and Visual Basic on screen to F8 (step) through the VBA code. It used to show me what was happening in Excel as I stepped through each line of code. For example I would F8 on a line of code like 'Sheets("list").Select' and Excel would flip over to the "list" worksheet before my eyes. So I have two questions.....one, how do I get Excel to perform as I step through each line of VBA code so I can see what's happening and two, any ideas on why I'm getting an error 440? Here's the code: Sub RunReport() ' ' RunReport Macro ' Macro recorded 9/7/2005 by CR28012 Dim strBottom As Integer Dim strLocation As String 'clear the old "YTD dir bonus summary" page Sheets("YTD dir bonus summary").Activate ActiveSheet.Outline.ShowLevels RowLevels:=2, ColumnLevels:=2 Rows("9:9").Select Range(Selection, Selection.End(xlDown)).Select Selection.ClearContents 'clear the old "YTD asst bonus summary" page Sheets("YTD asst bonus summary").Activate ActiveSheet.Outline.ShowLevels RowLevels:=2, ColumnLevels:=2 Rows("9:9").Select Range(Selection, Selection.End(xlDown)).Select Selection.ClearContents 'Select the list of stores (range) on "scroll list" sheet Sheets("scroll list").Activate Range("a1").Select Selection.End(xlDown).Select strBottom = ActiveCell.Row Range(Range("A1").Address & ":" & "A" & strBottom).Select 'Loop through each location For Each cell In Selection Sheets("scroll list").Select Range(cell.Address).Copy Sheets("Template").Select Range("B1").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False ActiveSheet.Calculate strLocation = Range("B1").Value ActiveSheet.Outline.ShowLevels RowLevels:=1, ColumnLevels:=1 'Create new sheet for location and name it ActiveSheet.Copy Befo=Sheets("Template") ActiveSheet.Name = Trim(strLocation) 'Select cells and replace formulas with values Cells.Select Selection.Copy Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False 'Cells.Replace What:="0", Replacement:="0", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False Range("A1").Select 'fill in the next line of the "YTD dir bonus summary" sheet Sheets("YTD dir bonus summary").Select ActiveSheet.Calculate Rows("5:5").Select Selection.Copy Range("a65000").Select Selection.End(xlUp).Select ActiveCell.Offset(1, 0).Activate Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Selection.Rows.Ungroup 'fill in the next line of the "YTD asst bonus summary" sheet Sheets("YTD asst bonus summary").Select ActiveSheet.Calculate Rows("5:5").Select Selection.Copy Range("a65000").Select Selection.End(xlUp).Select ActiveCell.Offset(1, 0).Activate Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Selection.Rows.Ungroup Next Sheets("YTD dir bonus summary").Select ActiveSheet.Outline.ShowLevels RowLevels:=1, ColumnLevels:=1 Range("A1").Select Sheets("YTD asst bonus summary").Select ActiveSheet.Outline.ShowLevels RowLevels:=1, ColumnLevels:=1 Range("A1").Select 'Hide working sheets Sheets("Template").Visible = False Sheets("Instructions").Visible = False Sheets("str list").Visible = False Sheets("SOSP03").Visible = False Sheets("SOSP03 YTD").Visible = False Sheets("ident sales").Visible = False Sheets("ident sales YTD").Visible = False Sheets("not ident history").Visible = False Sheets("SOSP04-Inv").Visible = False Sheets("SOSP05-labor actuals").Visible = False Sheets("SOSP05 YTD-labor actuals").Visible = False Sheets("Gordy's labor bud").Visible = False Sheets("Gordy's labor bud YTD").Visible = False Sheets("Poulsen's P&G focus QTR").Visible = False Sheets("Gary's bonus").Visible = False Sheets("Hal's out of stock").Visible = False Sheets("Cust 1st fr Mys Shop").Visible = False Sheets("Sales Brackets").Visible = False Sheets("Mys Shop Goals").Visible = False Sheets("Key Retailing").Visible = False Sheets("Rod's Turnover").Visible = False Sheets("Mark's Safety").Visible = False Sheets("Bill's Loyalty").Visible = False Sheets("Points Summary").Visible = False Sheets("scroll list").Visible = False End Sub -- Thanks, PTweety |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Excel not showing steps as I F8 through each line of VBA code
Youshould use Row.Count as the last row in the worksheet because it could
change. try this from Range("a65000").Select to Range("A" & Rows.Count).Select try this is all the places in the code. "pickytweety" wrote: I recently switched from Excel 2003 to 2007. A macro I had would vlookup information for a particular store into a template sheet, copy that sheet, paste it as values, and move to the next store. This macro isn't working anymore. I get an error 440 and it closes Excel for me. So I tried going showing both Excel and Visual Basic on screen to F8 (step) through the VBA code. It used to show me what was happening in Excel as I stepped through each line of code. For example I would F8 on a line of code like 'Sheets("list").Select' and Excel would flip over to the "list" worksheet before my eyes. So I have two questions.....one, how do I get Excel to perform as I step through each line of VBA code so I can see what's happening and two, any ideas on why I'm getting an error 440? Here's the code: Sub RunReport() ' ' RunReport Macro ' Macro recorded 9/7/2005 by CR28012 Dim strBottom As Integer Dim strLocation As String 'clear the old "YTD dir bonus summary" page Sheets("YTD dir bonus summary").Activate ActiveSheet.Outline.ShowLevels RowLevels:=2, ColumnLevels:=2 Rows("9:9").Select Range(Selection, Selection.End(xlDown)).Select Selection.ClearContents 'clear the old "YTD asst bonus summary" page Sheets("YTD asst bonus summary").Activate ActiveSheet.Outline.ShowLevels RowLevels:=2, ColumnLevels:=2 Rows("9:9").Select Range(Selection, Selection.End(xlDown)).Select Selection.ClearContents 'Select the list of stores (range) on "scroll list" sheet Sheets("scroll list").Activate Range("a1").Select Selection.End(xlDown).Select strBottom = ActiveCell.Row Range(Range("A1").Address & ":" & "A" & strBottom).Select 'Loop through each location For Each cell In Selection Sheets("scroll list").Select Range(cell.Address).Copy Sheets("Template").Select Range("B1").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False ActiveSheet.Calculate strLocation = Range("B1").Value ActiveSheet.Outline.ShowLevels RowLevels:=1, ColumnLevels:=1 'Create new sheet for location and name it ActiveSheet.Copy Befo=Sheets("Template") ActiveSheet.Name = Trim(strLocation) 'Select cells and replace formulas with values Cells.Select Selection.Copy Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False 'Cells.Replace What:="0", Replacement:="0", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False Range("A1").Select 'fill in the next line of the "YTD dir bonus summary" sheet Sheets("YTD dir bonus summary").Select ActiveSheet.Calculate Rows("5:5").Select Selection.Copy Range("a65000").Select Selection.End(xlUp).Select ActiveCell.Offset(1, 0).Activate Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Selection.Rows.Ungroup 'fill in the next line of the "YTD asst bonus summary" sheet Sheets("YTD asst bonus summary").Select ActiveSheet.Calculate Rows("5:5").Select Selection.Copy Range("a65000").Select Selection.End(xlUp).Select ActiveCell.Offset(1, 0).Activate Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Selection.Rows.Ungroup Next Sheets("YTD dir bonus summary").Select ActiveSheet.Outline.ShowLevels RowLevels:=1, ColumnLevels:=1 Range("A1").Select Sheets("YTD asst bonus summary").Select ActiveSheet.Outline.ShowLevels RowLevels:=1, ColumnLevels:=1 Range("A1").Select 'Hide working sheets Sheets("Template").Visible = False Sheets("Instructions").Visible = False Sheets("str list").Visible = False Sheets("SOSP03").Visible = False Sheets("SOSP03 YTD").Visible = False Sheets("ident sales").Visible = False Sheets("ident sales YTD").Visible = False Sheets("not ident history").Visible = False Sheets("SOSP04-Inv").Visible = False Sheets("SOSP05-labor actuals").Visible = False Sheets("SOSP05 YTD-labor actuals").Visible = False Sheets("Gordy's labor bud").Visible = False Sheets("Gordy's labor bud YTD").Visible = False Sheets("Poulsen's P&G focus QTR").Visible = False Sheets("Gary's bonus").Visible = False Sheets("Hal's out of stock").Visible = False Sheets("Cust 1st fr Mys Shop").Visible = False Sheets("Sales Brackets").Visible = False Sheets("Mys Shop Goals").Visible = False Sheets("Key Retailing").Visible = False Sheets("Rod's Turnover").Visible = False Sheets("Mark's Safety").Visible = False Sheets("Bill's Loyalty").Visible = False Sheets("Points Summary").Visible = False Sheets("scroll list").Visible = False End Sub -- Thanks, PTweety |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Adding a control line to excel chart without showing up in the leg | Charts and Charting in Excel | |||
| running macros, and not showing the steps | Excel Discussion (Misc queries) | |||
| Macros skip steps and/or stops in the middle of code | Excel Programming | |||
| A 2 line text showing up in the Cell in Excel prints in 1 line | Excel Discussion (Misc queries) | |||
| Clean Up Code - consolidate steps | Excel Discussion (Misc queries) | |||
Linear Mode
