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?
--
Thanks,
PTweety

As ALWAYS, post your code for comments and suggestions.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"pickytweety" wrote in message
...
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?
--
Thanks,
PTweety

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


"Don Guillett" wrote:

As ALWAYS, post your code for comments and suggestions.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"pickytweety" wrote in message
...
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?
--
Thanks,
PTweety

PS I already tried changing Paste:=xlValues to Paste:=xlPasteValues and it
didn't resolve my problem.
--
Thanks,
PTweety


"Don Guillett" wrote:

As ALWAYS, post your code for comments and suggestions.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"pickytweety" wrote in message
...
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?
--
Thanks,
PTweety