pivot tables in an excel macro

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

have a macro that worked in office 97 but am now getting an 1004 - addfields
in pivot table in error.

have checked the code against an excel macro book and looks to be in order.

why is it failing?
 
Bjornk01 said:
have a macro that worked in office 97 but am now getting an 1004 - addfields
in pivot table in error.
Click to expand...

Are you still using 97?
Can you post the code concerned, indicating the line which fails?

Bill Manville
MVP - Microsoft Excel, Oxford, England
 
no on the 97

code follows ...

Rows("1:2").Select
Selection.Delete Shift:=xlUp
Cells.Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"ffcnyipm!C1:C13").CreatePivotTable TableDestination:="",
TableName:= _
"PivotTable1"
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables("PivotTable1").SmallGrid = False
ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:=Array( _
"Group number and name", "PCP"), ColumnFields:="Zip code"
ActiveSheet.PivotTables("PivotTable1").PivotFields("PCP").Orientation = _
xlDataField
ActiveSheet.PivotTables("PivotTable1").PivotSelect "", xlDataAndLabel
Range("B5").Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Group number
and name" _
)
.PivotItems("(blank)").Visible = False
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Zip code")
.PivotItems(" ").Visible = False
.PivotItems("(blank)").Visible = False
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("PCP")
.PivotItems("(blank)").Visible = False
End With
ChDir "E:\adhoc_team\jnewland\erfreqflyer\March's data"
ActiveWorkbook.SaveAs Filename:= _
"E:\adhoc_team\jnewland\erfreqflyer\March's data\ffcnyipm.xls",
FileFormat:= _
xlExcel9795, Password:="", WriteResPassword:="",
ReadOnlyRecommended:= _
False, CreateBackup:=False
ActiveWorkbook.Close
End Sub

fails at line 10 and 11
ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:=Array( _
"Group number and name", "PCP"), ColumnFields:="Zip code"
 
Not yet.
Am on vacation with limited time online.

Bill Manville
MVP - Microsoft Excel, Oxford, England
 
Back
Top