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?
"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:=xlDatabase, 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:=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.
|
Similar Threads
