Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot Tables in VBA: Problem with AddFields Method
how to change that source data
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot tables problem | Excel Discussion (Misc queries) | |||
How to set default calculation method in pivot tables? | Excel Discussion (Misc queries) | |||
Please help: Pivot Tables problem | Excel Discussion (Misc queries) | |||
addfields method error | Excel Programming | |||
Runtime Error '1004' - AddFields method of PivotTable class failed | Excel Programming |