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.

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.

Thanks. I tried substituting what I had for what you coded below, but when I
do, the first section:
With ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, 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?



"joel" wrote:

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.

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:=xlDatab ase, SourceData:= _
srcdata).CreatePivotTable(TableDestination:="Count s!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.

"joel" wrote:

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.