Excel 2003 VBA crashes Excel 2007?

[newnewbie]

Hi,
Our company recently transitioned to Office 2007 and one of my macros crashes Excel 2007 - gives "Excel encountered a serious error and needs to close" always at the same spot of the code. I combed the web to see if there are grave differences between VBA in 2003 and 2007 and found nothing...this is the code. I would appreciate any suggestions!

'Application.Run "INTRANET.XLSM!Revenue_Format"
 
Sub Revenue_Format()
 
    areaCount = ActiveCell.CurrentRegion.Rows.Count
    If areaCount < 1 Then
        GoTo End_Format:
    Else
        GoTo Over_65000:
    End If
 
Over_65000:
 
    areaCount = ActiveCell.CurrentRegion.Rows.Count
    If areaCount > 65500 Then
        GoTo End_Format:
    Else
        GoTo Continue_Format:
    End If
 
Continue_Format:
 
    Range("H1").Select
    ActiveCell.FormulaR1C1 = "Parent Principal"
 
    Range("A1:L1").Select
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .ShrinkToFit = False
        .MergeCells = False
    End With
    Selection.Font.Bold = True
    Columns("I:I").Select
    Selection.NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ""-""_);_(@_)"
    Columns("A:A").Select
    Selection.NumberFormat = "mmm-yy"
    Cells.Select
    Cells.EntireColumn.AutoFit
    Range("A1").Select
    ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _
        ActiveCell.CurrentRegion, TableDestination:="", TableName:= _
        "PivotTable1"
    ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:="Parent Principal", _
        ColumnFields:="Period", PageFields:=Array("Region", "Market", "P&L", _
        "Branch Name", "Account", "Principal", "Customer Name", "Division", "Category")
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Amount")
        .Orientation = xlDataField
        .NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ""-""_);_(@_)"
    End With
    Range("A10").Select
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Data").PivotItems( _
        "Sum of Amount").Name = "Revenue Report"
    Range("C1").Select
    Selection.EntireRow.Insert
    Range("A1").Select
 
    Application.Run "INTRANET.XLSM!Delete_Data"
 
    ActiveWorkbook.Save
    ActiveWindow.Close
 
End_Format:
 
End Sub
 

[ChipR]

You can put a breakpoint in at the top of the function and step through until you find an error, or you can put in error handling and give yourself a msgbox telling you what the error is.

[NeoPa]

With 72 lines of code you will need to specify which of them the error occurs on.

[newnewbie]

NeoPa,
The problem is, I do not know. I do not get the VBA error, I get the "Excel encountered a serious error and needs to close". I suspect some part of the code that manipulates the pivottable is no longer valid, since there were sone significant changes to the pivottable format in the new Excel. I'll take ChipR advice, pull the code into a new test workbook and step through the code one line at a time until I find the error. I'll repost if I am still clueless :)
Thank you all!

[ChipR]

If you use error handling in all your code, you can catch these errors and give a helpful message box rather than crashing.

On Error GoTo ErrorHandler
 
    'code
 
ExitCode:
    Exit Function
 
ErrorHandler:
    MsgBox Err.number & " " & Err.Description
    Resume ExitCode

[newnewbie]

I'll try that! Thanks!

[newnewbie]

It fails at line 59

ActiveSheet.PivotTables("PivotTable1").PivotFields ("Data").PivotItems( _
"Sum of Amount").Name = "Revenue Report"

??

[ChipR]

Maybe you can get a more specific error with the message box.

[NeoPa]

This link may help (Debugging in VBA).

If it doesn't include it already, remember to use Option Explicit and to compile your project before proceeding to debug.

[newnewbie]

Message box gives me 0...

[newnewbie]

Used Option Explicit and compiled. Nothing.

[NeoPa]

@newnewbie
What happened when you traced through the code?

[ChipR]

@newnewbie
What does this mean?

[newnewbie]

If the errorhandler code is in there, when I hit line59 of the code, it gives me the error message with single "0" in it and the code stops. If I take eerorhandler out, Excel crashes with the same "Excel encountered a serious error and needs to close".

[newnewbie]

[IMG]C:\Documents and Settings\lskalska\Desktop\Error.xlsx[/IMG]

[ChipR]

Strange, error 0 would actually mean no error. I haven't used pivot tables, so my only guess would be to make sure that "Data" and "Sum of Amount" do actually exist.

[newnewbie]

[IMG]C:\Documents and Settings\lskalska\Desktop\Error.jpg[/IMG]

[newnewbie]

[IMG]C:\Documents and Settings\lskalska\Desktop\error2.jpg[/IMG]

[newnewbie]

Ok, thank you everybody. I have until next Wed to resolve this or else I will have to format close to a hundred pivots by hand :)

[newnewbie]

I did it!
Old code line 59-60:
ActiveSheet.PivotTables("PivotTable1").PivotFields ("Data").PivotItems( _
"Sum of Amount").Name = "Revenue Report"

New code (changes in bold):

ActiveSheet.PivotTables("PivotTable1").DataPivotField.PivotItems("Sum of Amount").Caption = "Revenue Report"

THANK YOU ALL!

[ChipR]

Glad to hear you figured it out. I'm sure other people will have the same problem, and your solution will help them.

[newnewbie]

Now I know how to use the errorhandler :)
Thanks!