Pivot Table question

  • Thread starter Thread starter Roger Converse
  • Start date Start date
R

Roger Converse

There are a total of 4 columns that I am trying to pivot. When I step through
the macro, the pivot table is created except only one of the four field
fields is available in the field list. Therefore, when it goes to "drag" one
of the items that is not on the field list to the pivot table, it can't find
the field name.

I am guessing that somewhere in here, I need to specify the number of field
names or something, because it is after this step where the macro debugs.

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"tempPO_ExpSumm!C1:C4").CreatePivotTable TableDestination:="", TableName:= _
"PivotTable6", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select

Any additional assistance would be greatly appreciated.

Thank you,
Roger
 
Hi Roger,

I think the problem is with your SourceData:= "tempPO_ExpSumm!C1:C4").
Pivot tables normally look for fields in the columns and you've only
specified one column. Assuming your data is in columns C to J try making the
statement
SourceData:= "tempPO_ExpSumm!C1:J4"
I think that will work. You may need to transpose your table if the field
names are in c1, c2, c3 and c4 so that the field names are in columns not
rows.

Another way to handle the source data would be: If Product is the name of a
field and all your field names are in contiguous columns then dim a range for
the data source. Do a find on any valid field name and then set the range
you just dimed to the currentregion about that field name you found.

dim SCR as range
set f = cells.find("Product") 'do a find on any valid field name
if not f is nothing then
f.select
end if

set SCR = activecell.currentregion

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
SCR).CreatePivotTable TableDestination:="", TableName:= _
"PivotTable6", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select

That way you don't need to worry about the dimensions of the source range.
You just need the field names in columns.

Regards,
Mike
 
Thank You!

MIKE215 said:
Hi Roger,

I think the problem is with your SourceData:= "tempPO_ExpSumm!C1:C4").
Pivot tables normally look for fields in the columns and you've only
specified one column. Assuming your data is in columns C to J try making the
statement
SourceData:= "tempPO_ExpSumm!C1:J4"
I think that will work. You may need to transpose your table if the field
names are in c1, c2, c3 and c4 so that the field names are in columns not
rows.

Another way to handle the source data would be: If Product is the name of a
field and all your field names are in contiguous columns then dim a range for
the data source. Do a find on any valid field name and then set the range
you just dimed to the currentregion about that field name you found.

dim SCR as range
set f = cells.find("Product") 'do a find on any valid field name
if not f is nothing then
f.select
end if

set SCR = activecell.currentregion

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
SCR).CreatePivotTable TableDestination:="", TableName:= _
"PivotTable6", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select

That way you don't need to worry about the dimensions of the source range.
You just need the field names in columns.

Regards,
Mike
Click to expand...
 
Back
Top