Pivot Tables in VBA: Problem with AddFields Method
 

Your source data range isn't right: All_Data!C1:C24

Change that range so it includes all your headings and data rows, and
the code may work correctly.

jimec74 wrote:
Hi

I seem to be having a problem adding fields to my Pivot Table in VBA. When
I reach the line: "ActiveSheet.PivotTables("RepData").AddFields. .." below, I
get a Run Time Errorr (1004) stating "AddFields method of PivotTable class
failed".

Can someone please point me in the right direction?

Thanks,

James

===================================

Sub RunDailyReport(ByVal ShiftDate As Date, ByVal Shift As String)

'Clear existing PivotTable data:
Sheets("Rep_Daily").Activate
Rows("73:73").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents

'Create a new Pivot Table:
Sheets("All_data").Activate
Cells.Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"All_Data!C1:C24").CreatePivotTable TableDestination:= _
"'Rep_Daily'!R76C2", TableName:="RepData" _
, DefaultVersion:=xlPivotTableVersion10
Sheets("Rep_Daily").Activate
ActiveSheet.PivotTables("RepData").AddFields RowFields:="LoadedBy1", _
ColumnFields:=Array("Material1", "PlantID"),
PageFields:=Array("Date", "Shift")
With ActiveSheet.PivotTables("RepData").PivotFields("Lo ads1")
.Orientation = xlDataField
.Caption = "Sum of Loads1"
.Function = xlSum
End With
ActiveWorkbook.ShowPivotTableFieldList = True
ActiveSheet.PivotTables("RepData").PivotFields("Da te").CurrentPage =
ShiftDate
Select Case Shift
Case "D"

ActiveSheet.PivotTables("RepData").PivotFields("Sh ift").CurrentPage = "D"
Case "N"

ActiveSheet.PivotTables("RepData").PivotFields("Sh ift").CurrentPage = "N"
Case "Both"

ActiveSheet.PivotTables("RepData").PivotFields("Sh ift").CurrentPage = "(All)"
End Select
With ActiveSheet.PivotTables("RepData").PivotFields("Pl antID")
.PivotItems("EX8001").Visible = False
.PivotItems("(blank)").Visible = False
End With

End Sub

====================================


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Pivot Tables in VBA: Problem with AddFields Method
 

Thanks Debra

That got it working! Seems like a simple error in retrospect...

Thanks again,

James


"Debra Dalgleish" wrote:

Your source data range isn't right: All_Data!C1:C24

Change that range so it includes all your headings and data rows, and
the code may work correctly.

jimec74 wrote:
Hi

I seem to be having a problem adding fields to my Pivot Table in VBA. When
I reach the line: "ActiveSheet.PivotTables("RepData").AddFields. .." below, I
get a Run Time Errorr (1004) stating "AddFields method of PivotTable class
failed".

Can someone please point me in the right direction?

Thanks,

James

===================================

Sub RunDailyReport(ByVal ShiftDate As Date, ByVal Shift As String)

'Clear existing PivotTable data:
Sheets("Rep_Daily").Activate
Rows("73:73").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents

'Create a new Pivot Table:
Sheets("All_data").Activate
Cells.Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"All_Data!C1:C24").CreatePivotTable TableDestination:= _
"'Rep_Daily'!R76C2", TableName:="RepData" _
, DefaultVersion:=xlPivotTableVersion10
Sheets("Rep_Daily").Activate
ActiveSheet.PivotTables("RepData").AddFields RowFields:="LoadedBy1", _
ColumnFields:=Array("Material1", "PlantID"),
PageFields:=Array("Date", "Shift")
With ActiveSheet.PivotTables("RepData").PivotFields("Lo ads1")
.Orientation = xlDataField
.Caption = "Sum of Loads1"
.Function = xlSum
End With
ActiveWorkbook.ShowPivotTableFieldList = True
ActiveSheet.PivotTables("RepData").PivotFields("Da te").CurrentPage =
ShiftDate
Select Case Shift
Case "D"

ActiveSheet.PivotTables("RepData").PivotFields("Sh ift").CurrentPage = "D"
Case "N"

ActiveSheet.PivotTables("RepData").PivotFields("Sh ift").CurrentPage = "N"
Case "Both"

ActiveSheet.PivotTables("RepData").PivotFields("Sh ift").CurrentPage = "(All)"
End Select
With ActiveSheet.PivotTables("RepData").PivotFields("Pl antID")
.PivotItems("EX8001").Visible = False
.PivotItems("(blank)").Visible = False
End With

End Sub

====================================


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Pivot Tables in VBA: Problem with AddFields Method
 

how to change that source data