Excel 2000 SP3 versus Excel 2007 --error code 0Xc0000005

[pickytweety]

Hi,
We recently converted from Excel 2000 SP3 to Excel 2007. Now when I run the
macro below, Excel creates all the sheets I ask it to (one sheet for each
location), but then before I can save or do anything else I get the error
message that Excel needs to close. When I click to see what information is
in the error report there is a code 0Xc0000005. So then I tried to run the
macro on a different computer (Excel 2007) and got the same error. Why won't
this run in the new version of Excel?

Thanks
PTweety



Sub RunReport()

Dim strLocation As String
Dim rngLoop As Range
Dim rngCell As Range
Dim wksTemp As Worksheet
Dim wksScroll As Worksheet
Dim wksNew As Worksheet
Dim wksDirBonus As Worksheet
Dim wksAstBonus As Worksheet
Dim rngfill As Range

'set the Template and Scroll List worksheets as objects
Set wksTemp = Sheets("Template")
Set wksScroll = Sheets("scroll list")
Set wksDirBonus = Sheets("YTD dir bonus summary")
Set wksAstBonus = Sheets("YTD asst bonus summary")

'clear the old "YTD dir bonus summary" page
With wksDirBonus
.Range("a9", .Range("a9").End(xlDown)).EntireRow.ClearContents
.Rows("2:5").Ungroup
.Rows("8:8").Ungroup
.Outline.ShowLevels RowLevels:=2, ColumnLevels:=2
End With

'clear the old "YTD asst bonus summary" page
With wksAstBonus
.Range("a9", .Range("a9").End(xlDown)).EntireRow.ClearContents
.Rows("2:5").Ungroup
.Rows("8:8").Ungroup
.Outline.ShowLevels RowLevels:=2, ColumnLevels:=2
End With

'Select the list of stores (range) on "scroll list" sheet
With wksScroll
Set rngLoop = .Range("a1", .Range("a1").End(xlDown))
End With

'show outline levels on wksTemp
wksTemp.Outline.ShowLevels RowLevels:=1, ColumnLevels:=1

'Loop through each cell in rngLoop
For Each rngCell In rngLoop
With wksTemp
..Range("B1").Value = rngCell
..Calculate
strLocation = .Range("B1").Value
End With

'Create new sheet for strLocation and name it
wksTemp.Copy Before:=wksTemp
Set wksNew = ActiveSheet

With wksNew
..Name = Trim(strLocation)


'Select cells and replace formulas with values
..Cells.Copy
..Cells.PasteSpecial Paste:=xlValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
application.CutCopyMode = False
Range("A1").Select ' right here

End With

'fill in the next line of wksDirBonus
CopyToNext wksDirBonus

'fill in the next line of wksAstBonus
CopyToNext wksAstBonus
Next

wksDirBonus.Rows("2:5").Group
wksDirBonus.Rows("8:8").Group
wksAstBonus.Rows("2:5").Group
wksAstBonus.Rows("8:8").Group
wksDirBonus.Outline.ShowLevels RowLevels:=1, ColumnLevels:=1
wksAstBonus.Outline.ShowLevels RowLevels:=1, ColumnLevels:=1

'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("John's Safety").Visible = False
Sheets("Thats Our Promise").Visible = False
Sheets("Assoc Tracker").Visible = False
Sheets("Controllable").Visible = False
Sheets("Ranking").Visible = False
Sheets("scroll list").Visible = False

End Sub

 

[Dave Peterson]

If the macro finishes, then the workbook crashes, I don't think it's your macro.

I think your workbook is getting corrupted (or may have been corrupted).

Can you create a small test workbook with just enough data to test and put the
macro in there to see if it runs ok and you can still work on it?

If you can (and I bet you can!), it may be time to rebuild the troublesome
workbook (ugh! not a pleasant task).

 

[pickytweety]

But it continues to run just fine on my old box (PC).

 

[Dave Peterson]

Each version of excel has a different tolerance for corruption.