Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old April 21st 08, 03:14 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 2,979
Default 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   Report Post  
Old April 21st 08, 10:19 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2008
Posts: 3
Default 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   Report Post  
Old September 21st 18, 12:15 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2018
Posts: 1
Default Pivot Tables in VBA: Problem with AddFields Method

how to change that source data


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Pivot tables problem Amin Excel Discussion (Misc queries) 1 April 26th 10 02:57 PM
How to set default calculation method in pivot tables? Larryq22 Excel Discussion (Misc queries) 2 July 20th 09 02:10 PM
Please help: Pivot Tables problem James Yeang Excel Discussion (Misc queries) 5 April 25th 06 01:45 AM
addfields method error jnewl Excel Programming 0 August 31st 05 02:13 PM
Runtime Error '1004' - AddFields method of PivotTable class failed MikeC[_3_] Excel Programming 1 May 21st 04 06:58 AM


All times are GMT +1. The time now is 11:52 AM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017