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.Piv otTables("Pivot Table1").PivotF ields("Data").P ivotItems( _
"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]

Used Option Explicit and compiled. Nothing.

What happened when you traced through the code?

[ChipR]

Message box gives me 0...

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\lskals ka\Desktop\Erro r.xlsx[/IMG]