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:=xlDatab ase, SourceData:= _
srcdata).CreatePivotTable TableDestination:="Counts!R3C1" _
, TableName:="PivotTable2", DefaultVersion:=xlPivotTableVersion10

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

.PivotFields("id").Orientation = _
xlDataField
end with


"SteveM" wrote:

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

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, 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:=xlDatab ase, 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.