VBA code for making a pivot table
 

I have written some code to make a pivot table from a sheet of data. When I
run it, however, it is eliminating some data in the table (not including it).
If I do the pivot table by hand, all the data is included. What is wrong
with my code?
What it is eliminating is some column fields: it includes 11, but leaves out
7. Can you tell me what I am doing wrong? Thanks!
Here is my code:

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"data!C1:C27").CreatePivotTable TableDestination:="Pivot_table!R3C1",
TableName:= _
"PivotTable1", DefaultVersion:=xlPivotTableVersion10

ThisWorkbook.Sheets("Pivot_table").Select


ActiveSheet.Cells(3, 1).Select

ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:=Array("day", _
"Data"), ColumnFields:="country", PageFields:="year"


ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:=Array("day", _
"Data"), ColumnFields:="country", PageFields:="year"
With ActiveSheet.PivotTables("PivotTable1").PivotFields ("new unique
senders")
.Orientation = xlDataField
.Caption = "Sum of new unique senders"
.Position = 1
.Function = xlSum
End With

VBA code for making a pivot table
 

Your SourceData only includes column C: "data!C1:C27"

It should probably include more columns. Instead of the range address,
you could use a dynamic range as the pivot source, and refer to the
range name in the code. There are instructions he

http://www.contextures.com/xlPivot01.html

KyWilde wrote:
I have written some code to make a pivot table from a sheet of data. When I
run it, however, it is eliminating some data in the table (not including it).
If I do the pivot table by hand, all the data is included. What is wrong
with my code?
What it is eliminating is some column fields: it includes 11, but leaves out
7. Can you tell me what I am doing wrong? Thanks!
Here is my code:

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"data!C1:C27").CreatePivotTable TableDestination:="Pivot_table!R3C1",
TableName:= _
"PivotTable1", DefaultVersion:=xlPivotTableVersion10

ThisWorkbook.Sheets("Pivot_table").Select


ActiveSheet.Cells(3, 1).Select

ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:=Array("day", _
"Data"), ColumnFields:="country", PageFields:="year"


ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:=Array("day", _
"Data"), ColumnFields:="country", PageFields:="year"
With ActiveSheet.PivotTables("PivotTable1").PivotFields ("new unique
senders")
.Orientation = xlDataField
.Caption = "Sum of new unique senders"
.Position = 1
.Function = xlSum
End With


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html