We help IT Professionals succeed at work.

Excel 2010 - Pivot creation with VBA

csehz
csehz asked
on
Dear Experts,

Can you please have a short look on the attached code, basically the macro recorder is creating like this.

For me the problem is that it adds the new sheet as Sheet2, but on the worksheet where I would like to apply the macro, sometimes already has such one or the default would come as Sheet3 or Sheet4 etc. And in such cases error message comes.

Is there maybe some best practice to solve this? I assume at the Sheet.Add also a sheet name should be defined, and the pivot would be placed on that

thanks,
Sheets.Add
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Report_of_WC_Capacity!R1C1:R65536C17", Version:=xlPivotTableVersion10). _
        CreatePivotTable TableDestination:="Sheet2!R3C1", TableName:="PivotTable1" _
        , DefaultVersion:=xlPivotTableVersion10
    Sheets("Sheet2").Select

Open in new window

Comment
Watch Question

I.T. Contractor
Commented:
when creating the sheet you can name it. Carry this name through the rest of your code.

so where you had sheet2 you now have myPivot.
Sheets.Add.name = "myPivot"
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Report_of_WC_Capacity!R1C1:R65536C17", Version:=xlPivotTableVersion10). _
        CreatePivotTable TableDestination:="myPivot!R3C1", TableName:="PivotTable1" _
        , DefaultVersion:=xlPivotTableVersion10
    Sheets("MyPivot").Select

Open in new window

csehzIT consultant

Author

Commented:
Ah so simply so applying Sheets.Add.name = "myPivot".

Thanks very much