Pivot table macro error

[Navvy]

Hello,

I have recorded three macros which create a pivot table one after the other on the sheet "Results". For some reason, sometimes it works and other times it crashes. I think it has something to do with the pivot table name - but I changed these. Here's my code:

Sub CreateGrid()

Application.CommandBars("PivotTable&quot.Visible = False
ActiveWorkbook.ShowPivotTableFieldList = False
ActiveWorkbook.ShowPivotTableFieldList = False



ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"Data!R1C1:R116C13&quot.CreatePivotTable TableDestination:= _
"[Inter.xls]Results!R2C1", TableName:="DateTable"
', DefaultVersion:= _
'xlPivotTableVersion10
ActiveSheet.PivotTables("DateTable&quot.AddFields RowFields:="Party", _
ColumnFields:="Type"
ActiveSheet.PivotTables("DateTable&quot.PivotFields("Type&quot.Orientation = _
xlDataField
ActiveWorkbook.ShowPivotTableFieldList = False
ActiveWorkbook.ShowPivotTableFieldList = False
Application.CommandBars("PivotTable&quot.Visible = False



End Sub
Sub CreateMth()

Application.CommandBars("PivotTable&quot.Visible = False
ActiveWorkbook.ShowPivotTableFieldList = False
ActiveWorkbook.ShowPivotTableFieldList = False



Sheets("Month&quot.Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"Month!R1C1:R116C13&quot.CreatePivotTable TableDestination:= _
"[Inter.xls]Results!R35C1", TableName:="MonthTable", DefaultVersion:= _
xlPivotTableVersion10
ActiveSheet.PivotTables("MonthTable&quot.AddFields RowFields:="Party", _
ColumnFields:="Type"
ActiveSheet.PivotTables("MonthTable&quot.PivotFields("Type&quot.Orientation = _
xlDataField
ActiveWorkbook.ShowPivotTableFieldList = False
ActiveWorkbook.ShowPivotTableFieldList = False
Application.CommandBars("PivotTable&quot.Visible = False



End Sub
Sub CreateWk()

Application.CommandBars("PivotTable&quot.Visible = False
ActiveWorkbook.ShowPivotTableFieldList = False
ActiveWorkbook.ShowPivotTableFieldList = False

Sheets("Week&quot.Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"Week!R1C1:R116C13&quot.CreatePivotTable TableDestination:= _
"[Inter.xls]Results!R75C1", TableName:="WeekTable", DefaultVersion:= _
xlPivotTableVersion10
ActiveSheet.PivotTables("WeekTable&quot.AddFields RowFields:="Party", _
ColumnFields:="Type"
ActiveSheet.PivotTables("WeekTable&quot.PivotFields("Type&quot.Orientation = _
xlDataField
ActiveWorkbook.ShowPivotTableFieldList = False
ActiveWorkbook.ShowPivotTableFieldList = False
Application.CommandBars("PivotTable&quot.Visible = False


End Sub

 

[xlbo]

and where, pray, does it crash ??
and what is the error message ??

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[Navvy]

Hi Geoff,

The error message is:
Unable to get the PivotTables property of the Worksheet class.

On debug:
ActiveSheet.PivotTables("MonthTable&quot.AddFields RowFields:="Party", _
ColumnFields:="Type"

 

[xlbo]

I think this may be your problem:

Sheets("Month&quot.Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"Month!R1C1:R116C13&quot.CreatePivotTable TableDestination:= "[Inter.xls]Results!R35C1",

The ACTIVE sheet is "Month" but the pivot table is on "Results" therefore:

ActiveSheet.PivotTables("MonthTable&quot.AddFields RowFields:="Party", _
ColumnFields:="Type"
is trying to find a pivot table called monthtable on the "Month" sheet when it is actually on the "Results" sheet

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244