Need help adding a pivot table to a new sheet using vba

[frank1107]

Hey all, I attempted to use record macro to record my steps to use at a later time. I got to the step of adding a pivot table to a new sheet before i received an error. I assume dit was because the sheet the pivot table was attempting to be added to already existed. I tried to use online sources to modify the code to create a new sheet to add the pivot table but still receive an error at this part of the code:

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"OmniPort!R1C1:R3405C16", Version:=xlPivotTableVersion10).CreatePivotTable _
TableDestination:=wsNew.Name & "!R1C16", TableName:="newPivotTable1", DefaultVersion _
:=xlnewPivotTableVersion10

Any idea why the error is still happening?

Thanks for the help.

 

[Colo]

Hi frank1107,

An argument used for DefaultVersion seems to be xlPivotTableVersion10. No need to add "new" I guess.

 

[frank1107]

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"OmniPort!R1C1:R3405C16", Version:=xlPivotTableVersion10).CreatePivotTable _
TableDestination:=wsNew.Name & "!R1C16", TableName:="PivotTable1", DefaultVersion _
:=xlPivotTableVersion10

deleted the "new" but still receiving the same error at the same point.

Thanks for the idea though!