Avoiding an extra sheet when creating a pivot table

[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.

 

[joel]

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

 

[StevenM]

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?

 

[StevenM]

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.