runtime error 5 with recorded macro

[hwkeyser]

Hi everyone,

i read a half dozen threads now trying to figure out why my simple, "delete old pivot table then add new pivot table" macro is returning runtime error 5.

This seems to be one of the recognized issues with XL'07, but i thought i'd put mine out there for assistance and guidance.

    Columns("A:A").Select
    Selection.Delete Shift:=xlToLeft
    Selection.Insert Shift:=xlToRight
    Range("A1").Select
[COLOR="Red"]        ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Compiled List!R1C1:R5985C7", Version:=xlPivotTableVersion12). _
        CreatePivotTable TableDestination:="'Upcoming and Current List'!R1C1", _
        TableName:="PivotTable3", DefaultVersion:=xlPivotTableVersion12[/COLOR]

The error stems from this point right here.

the error reads: invalid procedure call or argument.

Any help would be greatly appreciated,

Thanks,

HK

 

[vaskov17]

Do the sheets "Compiled List" and "Upcoming and Current List" exist in the workbook?

 

[VoG]

Try replacing ActiveWorkbook with ActiveSheet.

 

[hwkeyser]

yes. They are neither new nor modified in title.

Also, i've tried the names of both sheets with the single quotations encompassing (the above example only shows it around the destination).. the error occurs even when both are encompassed.

 

[hwkeyser]

Try replacing ActiveWorkbook with ActiveSheet.

Replacing ActiveWorkbook. with ActiveSheet. brought up runtime error 438: "object doesn't support this property or method"

 

[vaskov17]

I tried your code to create the pivot with different sheet names and smaller data and it worked fine. The three lines above it do give me an error (1004) when I try to delete the same pivot.

 

[hwkeyser]

I changed "Upcoming and Current Lists" to "Upcoming_and_Current_Lists" and now the thing works perfectly. I hate when its those sorts of things which stall up the application.