run error 1004 - error when trying to build a pivot table thru an

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

Guest

get run error 1004 - addfield error when trying to build a pivot table thru a
macro.

this macro worked fine under office 97. now that i have 2000, am getting
this error.

i submitted this request last week and for a while it looked as though
someone was working on the issue.

it is important that i get this solved quickly.

i have included the code and where it is failing. i can not see anything
wrong with the code. have checked various manuals and to me it seems in
order. obviously not, or it would not be failing.
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"
 
Jnewl said:
i submitted this request last week and for a while it looked as though
someone was working on the issue.
Click to expand...
As I indicated - I was on vacation and did not have access to all
versions of Excel. Sorry for the delay
ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:=Array( _
"Group number and name", "PCP"), ColumnFields:="Zip code"
Click to expand...

There is no obvious reason for this to fail in 2000 when it worked in 97.
Try one of the following
a)
ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:="Group
number and name"
ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:="PCP",
AddToTable:=True
ActiveSheet.PivotTables("PivotTable1").AddFields ColumnFields:="Zip
code", AddToTable:=True

or b)
ActiveSheet.PivotTables("PivotTable1").PivotFields("Group number and
name").Orientation = xlRowField
ActiveSheet.PivotTables("PivotTable1").PivotFields("PCP").Orientation =
xlRowField
ActiveSheet.PivotTables("PivotTable1").PivotFields("Zip
code").Orientation = xlColumnField

By splitting it up this way, if there is a problem with one of the fields
you will be able to tell which.

Bill Manville
MVP - Microsoft Excel, Oxford, England
 
thanks.
i tried the code and kept getting red lines. no syntax errors, but it did
not like the concepts.
trying another approach using your code.
will let you know
 
Back
Top