PivotTables in Excel 97? How to retrofit 2003 VBA macro?

[jmthompson]

Good morning,
I have created a "template" for not-so-savvy Excel users to automatically create a pivotTable from data the user pastes in the file. A few of the users run Excel 97 and have not been able to use the template. I am trying (without having access to 97) to retro-fit my macro for them. I have already "fixed" my find/replace code and my TTC code, now the macro is generating Error 438 "Object Does not support this property or method" Any ideas how I can fix this for them?
Debug highlights this code:

    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        PRange).CreatePivotTable TableDestination:= _
        "'Budget Pivot'!R1C1", TableName:="Budget Pivot", DefaultVersion:= _
        xlPivotTableVersion10

This is the complete code:

Sub Create_PivotTables()
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Columns("G:G").Select
    Selection.Insert Shift:=xlToRight
    Selection.Insert Shift:=xlToRight
    Columns("F:F").Select
    Selection.TextToColumns Destination:=Range("F1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, Other:=True, OtherChar _
        :="/", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1))
 
    Columns("F:H").Select
    Selection.NumberFormat = "General"
    Columns("G:G").Select
    Selection.Delete Shift:=xlToLeft
    Range("F1").Select
    ActiveCell.FormulaR1C1 = "Period"
    Range("G1").Select
    Selection.NumberFormat = "General"
    ActiveCell.FormulaR1C1 = "Year"
 
Last = Cells(Rows.Count, "A").End(xlUp).Row
For i = Last To 2 Step -1
If (Cells(i, "M").Value) = "WIRE PAYMENT" Then
Cells(i, "M").EntireRow.Delete
End If
Next i
For i = Last To 2 Step -1
If (Cells(i, "M").Value) = "WIRE/ACH PAYMENT" Then
Cells(i, "M").EntireRow.Delete
End If
Next i
    Columns("H:H").Select
    Selection.Style = "Currency"
 
    Columns("M:M").Select
Dim c As Range
        For Each c In Range([M2], Cells(Rows.Count, "M").End(xlUp))
            If c.Value Like "DHL*" Then c.Value = "DHL"
            If c.Value Like "*GRAINGER*" Then c.Value = "GRAINGER"
            If c.Value Like " ATT*" Then c.Value = "AT&T"
            If c.Value Like "AT&T*" Then c.Value = "AT&T"
            If c.Value Like "UPS*" Then c.Value = "UPS"
            If c.Value Like "KROGER*" Then c.Value = "KROGER"
            If c.Value Like "LOWES*" Then c.Value = "LOWES"
            If c.Value Like "LOWE'S*" Then c.Value = "LOWES"
            If c.Value Like "MEIJER*" Then c.Value = "MEIJER"
            If c.Value Like "OFFICE DEPOT*" Then c.Value = "OFFICE DEPOT"
            If c.Value Like "STAPLES*" Then c.Value = "STAPLES"
            If c.Value Like "AMOCO*" Then c.Value = "AMOCO"
            If c.Value Like "BEST BUY*" Then c.Value = "BEST BUY"
            If c.Value Like "BESTBUYCOM*" Then c.Value = "BEST BUY"
            If c.Value Like "TARGET*" Then c.Value = "TARGET"
            If c.Value Like "SHELL*" Then c.Value = "SHELL"
            If c.Value Like "WAL-MART*" Then c.Value = "WAL-MART"
            If c.Value Like "CORP EX*" Then c.Value = "CORP EX"
            If c.Value Like "CVS*" Then c.Value = "CVS"
            If c.Value Like "FEDEX*" Then c.Value = "FEDEX"
            If c.Value Like "THE HOME DEPOT*" Then c.Value = "THE HOME DEPOT"
            If c.Value Like "USPS*" Then c.Value = "USPS"
            If c.Value Like "WALGREEN*" Then c.Value = "WALGREEN"
            If c.Value Like "EXXONMOBIL*" Then c.Value = "EXXONMOBIL"
            If c.Value Like "DUNKIN*" Then c.Value = "DUNKIN"
            If c.Value Like "HARBOR FREIGHT TOOLS*" Then c.Value = "HARBOR FREIGHT TOOLS"
            If c.Value Like "MACY*" Then c.Value = "MACY'S"
            If c.Value Like "ARAMARK UNIFORM*" Then c.Value = "ARAMARK UNIFORM"
            If c.Value Like "ADVANCE AUTO PARTS*" Then c.Value = "ADVANCE AUTO PARTS"
            If c.Value Like "DOLRTREE*" Then c.Value = "DOLLARTREE"
            If c.Value Like "KMART*" Then c.Value = "KMART"
            If c.Value Like "XFR*" Then c.Value = "XFR"
            If c.Value Like "XFER*" Then c.Value = "XFR"
            If c.Value Like "BIG LOTS*" Then c.Value = "BIG LOTS"
            If c.Value Like "ACME MARKETS*" Then c.Value = "ACME MARKETS"
            If c.Value Like "ACO-HARDWARE*" Then c.Value = "ACO-HARDWARE"
            If c.Value Like "ALLTEL*" Then c.Value = "ALLTEL"
            If c.Value Like "APPLEBEE*" Then c.Value = "APPLEBEES"
            If c.Value Like "ARAMARK   *" Then c.Value = "ARAMARK"
            If c.Value Like "AUTO PARTS UNLIMITED*" Then c.Value = "AUTO PARTS UNLIMITED"
            If c.Value Like "AUTOZONE*" Then c.Value = "AUTOZONE"
            If c.Value Like "B AND B APPL*" Then c.Value = "B AND B APPL"
            If c.Value Like "BARNES & NOBLE*" Then c.Value = "BARNES & NOBLE"
            If c.Value Like "BATH & BODY WORKS*" Then c.Value = "BATH & BODY WORKS"
            If c.Value Like "B & H PHOTO*" Then c.Value = "B & H PHOTO"
            If c.Value Like "BATTERIES PLUS*" Then c.Value = "BATTERIES PLUS"
            If c.Value Like "BED BATH & BEYOND*" Then c.Value = "BED BATH & BEYOND"
            If c.Value Like "BELK*" Then c.Value = "BELK"
            If c.Value Like "BLOCKBUSTER VIDEO*" Then c.Value = "BLOCKBUSTER VIDEO"
            If c.Value Like "BORDERS BOOKS*" Then c.Value = "BORDERS BOOKS"
            If c.Value Like "BURGER KING*" Then c.Value = "BURGER KING"
            If c.Value Like "BUSCH'S*" Then c.Value = "BUSCH'S"
            If c.Value Like "CIRCUIT CITY*" Then c.Value = "CIRCUIT CITY"
            If c.Value Like "COMMUNITY COFFEE*" Then c.Value = "COMMUNITY COFFEE"
            If c.Value Like "COMPUSA*" Then c.Value = "COMPUSA"
            If c.Value Like "******* BARREL*" Then c.Value = "******* BARREL"
            If c.Value Like "D&W*" Then c.Value = "D&W"
            If c.Value Like "DOLLAR GEN*" Then c.Value = "DOLLAR GENERAL"
            If c.Value Like "DOMINOS*" Then c.Value = "DOMINOS"
            If c.Value Like "DOMINO'S*" Then c.Value = "DOMINOS"
            If c.Value Like "FELPAUSCH*" Then c.Value = "FELPAUSCH"
            If c.Value Like "FERGUSON ENT*" Then c.Value = "FERGUSON ENT"
            If c.Value Like "FOOD WORLD*" Then c.Value = "FOOD WORLD"
            If c.Value Like "GIANT FOOD*" Then c.Value = "GIANT FOOD"
            If c.Value Like "GIANT-EAGLE*" Then c.Value = "GIANT-EAGLE"
            If c.Value Like "gempler*" Then c.Value = "gempler"
            If c.Value Like "GODFATHERS PIZZA*" Then c.Value = "GODFATHERS PIZZA"
            If c.Value Like "HAPPY HARRY'S*" Then c.Value = "HAPPY HARRY'S"
            If c.Value Like "HOBBY LOBBY*" Then c.Value = "HOBBY LOBBY"
            If c.Value Like "JIMMY JOHN*" Then c.Value = "JIMMY JOHNS"
            If c.Value Like "JOANN FABRIC*" Then c.Value = "JOANN FABRIC"
            If c.Value Like "KOHL*" Then c.Value = "KOHLS"
            If c.Value Like "LABSAFE*" Then c.Value = "LABSAFE"
            If c.Value Like "LEXIS-NEXIS*" Then c.Value = "LEXIS-NEXIS"
            If c.Value Like "MARATHON OIL*" Then c.Value = "MARATHON OIL"
            If c.Value Like "MCDONALD'S*" Then c.Value = "MCDONALD'S"
            If c.Value Like "MENARDS*" Then c.Value = "MENARDS"
            If c.Value Like "MOTION INDUSTRIES*" Then c.Value = "MOTION INDUSTRIES"
            If c.Value Like "NEWARK INONE*" Then c.Value = "NEWARK INONE"
            If c.Value Like "NORDSTROM*" Then c.Value = "NORDSTROM"
            If c.Value Like "O'CHARLEY'S*" Then c.Value = "O'CHARLEY'S"
            If c.Value Like "OFFICE MAX*" Then c.Value = "OFFICE MAX"
            If c.Value Like "OUTBACK*" Then c.Value = "OUTBACK"
            If c.Value Like "PANERA BREAD*" Then c.Value = "PANERA BREAD"
            If c.Value Like "PAPA JOHNS*" Then c.Value = "PAPA JOHNS"
            If c.Value Like "PIZZA HUT*" Then c.Value = "PIZZA HUT"
            If c.Value Like "POPEYE*" Then c.Value = "POPEYES"
            If c.Value Like "RADIO SHACK*" Then c.Value = "RADIO SHACK"
            If c.Value Like "RADIOSHACK*" Then c.Value = "RADIO SHACK"
            If c.Value Like "ROTO-ROOTER*" Then c.Value = "ROTO-ROOTER"
            If c.Value Like "ROTO ROOTER*" Then c.Value = "ROTO-ROOTER"
            If c.Value Like "RUBY TUESDAY*" Then c.Value = "RUBY TUESDAY"
            If c.Value Like "RED LOBSTER*" Then c.Value = "RED LOBSTER"
            If c.Value Like "RITE AID*" Then c.Value = "RITE AID"
            If c.Value Like "SAFEWAY STORE*" Then c.Value = "SAFEWAY STORE"
            If c.Value Like "SAFEWAY GIFT*" Then c.Value = "SAFEWAY STORE"
            If c.Value Like "SEARS ROEBUCK*" Then c.Value = "SEARS ROEBUCK"
            If c.Value Like "SHERWIN WILLIAMS*" Then c.Value = "SHERWIN WILLIAMS"
            If c.Value Like "SPEEDWAY*" Then c.Value = "SPEEDWAY"
            If c.Value Like "STARBUCKS*" Then c.Value = "STARBUCKS"
            If c.Value Like "SUBWAY*" Then c.Value = "SUBWAY"
            If c.Value Like "OLIVE GARD*" Then c.Value = "OLIVE GARDEN"
            If c.Value Like "TRACTOR-SUPPLY*" Then c.Value = "TRACTOR SUPPLY"
            If c.Value Like "TRADER JOE'S*" Then c.Value = "TRADER JOE'S"
            If c.Value Like "AOL*" Then c.Value = "AOL"
            If c.Value Like "VERIZON WIRELESS*" Then c.Value = "VERIZON WIRELESS"
            If c.Value Like "VERIZON WRLS*" Then c.Value = "VERIZON WIRELESS"
            If c.Value Like "VRZWRLSS*" Then c.Value = "VERIZON WIRELESS"
            If c.Value Like "WALMART*" Then c.Value = "WAL MART"
            If c.Value Like "WESCO*" Then c.Value = "WESCO"
            If c.Value Like "WINN-DIXIE*" Then c.Value = "WINN-DIXIE"
            If c.Value Like "ZEE MEDICAL*" Then c.Value = "ZEE MEDICAL"
            If c.Value Like "agent fee*" Then c.Value = "AGENT FEE"
            If c.Value Like "*AMTRAK*" Then c.Value = "AMTRAK"
            If c.Value Like "AMZ~*KINDLE*" Then c.Value = "AMZ*KINDLE"
            If c.Value Like "APPLIED IND TECH*" Then c.Value = "APPLIED IND TECH"
            If c.Value Like "ARBY'S*" Then c.Value = "ARBY'S"
            If c.Value Like "ATTM~**" Then c.Value = "AT&T"
            If c.Value Like "ATT~**" Then c.Value = "AT&T"
            If c.Value Like "BIG Y*" Then c.Value = "BIG Y FOODS"
            If c.Value Like "BJ WHOLESALE*" Then c.Value = "BJ WHOLESALE"
            If c.Value Like "BP OIL*" Then c.Value = "BP"
            If c.Value Like "GFS MKTPLC*" Then c.Value = "GFS MKTPLC"
            If c.Value Like "HESS*" Then c.Value = "HESS"
            If c.Value Like "OFFICEMAX*" Then c.Value = "OFFICE MAX"
            If c.Value Like "PCC SALES*" Then c.Value = "PCC SALES"
            If c.Value Like "WITTICHEN SUPPLY*" Then c.Value = "WITTICHEN SUPPLY"
            If c.Value Like "VZWRLSS*" Then c.Value = "VERIZON WIRELESS"
            If c.Value Like "QWEST COMMERCIAL*" Then c.Value = "QWEST COMMERCIAL"
            If c.Value Like "QWEST ~*COMMERCIAL*" Then c.Value = "QWEST COMMERCIAL"
            If c.Value Like "QWESTCOMM*" Then c.Value = "QWEST COMMERCIAL"
            If c.Value Like "SAKS FIFTH*" Then c.Value = "SAKS FIFTH AVE"
            If c.Value Like "SAFEWAY TIRE*" Then c.Value = "SAFEWAY TIRE"
            If c.Value Like "SEARS.COM*" Then c.Value = "SEARS ROEBUCK"
            If c.Value Like "*STOP & SHOP*" Then c.Value = "STOP & SHOP"
            If c.Value Like "xerox*" Then c.Value = "XEROX"
            If c.Value Like "XPEDX*" Then c.Value = "XPEDX"
        Next c
 
 
 
For i = Last To 2 Step -1
If (Cells(i, "M").Value) = "XFR" Then
Cells(i, "M").EntireRow.Delete
End If
Next i
 
lastRow = Cells(Rows.Count, "A").End(xlUp).Row
myLastCell = Cells(lastRow, 24).Address
Set PRange = Range("A1:" & myLastCell)
Application.CommandBars("PivotTable").Visible = False
    Range("A1:" & myLastCell).Select
 
    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        PRange).CreatePivotTable TableDestination:= _
        "'Budget Pivot'!R1C1", TableName:="Budget Pivot", DefaultVersion:= _
        xlPivotTableVersion10
    Sheets("Budget Pivot").PivotTables("Budget Pivot").AddFields RowFields:=Array( _
        "DEPARTMENT", "G/L ACCOUNT", "Orig Merchant Name"), ColumnFields:="Period", PageFields:="Year"
    Sheets("Budget Pivot").PivotTables("Budget Pivot").PivotFields("Transaction Amount"). _
        Orientation = xlDataField
    ActiveWorkbook.ShowPivotTableFieldList = False
 
Sheets("Budget Pivot").Select
    ActiveSheet.PivotTables("Budget Pivot").PivotFields("DEPARTMENT").NumberFormat _
        = "0_);(0)"
    Range("B7").Select
    ActiveSheet.PivotTables("Budget Pivot").PivotFields("G/L ACCOUNT"). _
        NumberFormat = "0_);(0)"
 
lastRow = Cells(Rows.Count, "F").End(xlUp).Row
myLastCell = Cells(lastRow, 26).Address
Range("D5:" & myLastCell).Style = "Currency"
Range("A:A").ColumnWidth = 26.29
Sheets("Data Dump").Select
Range("A1").Select
    Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, Tab:=True, OtherChar _
        :="/", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, _
        1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12 _
        , 1), Array(13, 1), Array(14, 1), Array(15, 1), Array(16, 1), Array(17, 1), Array(18, 1), _
        Array(19, 1), Array(20, 1), Array(21, 1), Array(22, 1), Array(23, 1), Array(24, 1))
 
Sheets("Budget Pivot").Select
    Cells.Select
    Cells.EntireColumn.AutoFit
Range("A1").Select
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub