Avoiding an extra sheet when creating a pivot table

  • Thread starter Thread starter SteveM
  • Start date Start date
S

SteveM

I have this code now:
ActiveSheet.Cells(1, 1).Select
ActiveCell.CurrentRegion.Select
srcdata = ActiveCell.CurrentRegion.Address

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
srcdata).CreatePivotTable TableDestination:="" _
, TableName:="PivotTable2", DefaultVersion:=xlPivotTableVersion10

ActiveSheet.PivotTableWizard TableDestination:="Counts!R3C1"

ActiveSheet.PivotTables("PivotTable2").AddFields RowFields:=Array( _
"FeatureName", "State"), ColumnFields:="Severity"

ActiveSheet.PivotTables("PivotTable2").PivotFields("id").Orientation = _
xlDataField


Which inserts an extra sheet. What would seem logical to me is to give the
table destination a value in the first statement instead of adding it as its
own line. So I thought I could do this:
ActiveSheet.Cells(1, 1).Select
ActiveCell.CurrentRegion.Select
srcdata = ActiveCell.CurrentRegion.Address

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
srcdata).CreatePivotTable TableDestination:="Counts!R3C1" _
, TableName:="PivotTable2", DefaultVersion:=xlPivotTableVersion10

ActiveSheet.PivotTables("PivotTable2").AddFields RowFields:=Array( _
"FeatureName", "State"), ColumnFields:="Severity"

ActiveSheet.PivotTables("PivotTable2").PivotFields("id").Orientation = _
xlDataField


But then I get an error when it reaches the next statement setting the row
and column fields. How do I give a destination in the first statement? Or is
there a way to combine all of it into one statement?

Thanks in advance,
S.
 
You don't know the name of the pivot table that is being added. that is why
you are getting the error. Try this

with ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
srcdata).CreatePivotTable TableDestination:="Counts!R3C1" _
, TableName:="PivotTable2", DefaultVersion:=xlPivotTableVersion10

.AddFields RowFields:=Array( _
"FeatureName", "State"), ColumnFields:="Severity"

.PivotFields("id").Orientation = _
xlDataField
end with
 
Thanks. I tried substituting what I had for what you coded below, but when I
do, the first section:
With ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
srcdata).CreatePivotTable TableDestination:="Counts!R3C1" _
, TableName:="PivotTable2", DefaultVersion:=xlPivotTableVersion10

is in red with an error message saying "compile error: expected end of
statement" and the TableDestination is highlighted.

Any idea what I can try next?
 
Forget my previous note. I tracked down the problem to missing parentheses
after the CreatePivotTable piece. I ended up with this:

With ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
srcdata).CreatePivotTable(TableDestination:="Counts!R3C1", _
TableName:="PivotTable2", _
DefaultVersion:=xlPivotTableVersion10)

.AddFields RowFields:=Array( _
"FeatureName", "State"), ColumnFields:="Severity"

.PivotFields("id").Orientation = _
xlDataField
End With

And that seemed to do it. Thank you for pointing me in the right direction.
s.
 
Back
Top