CODEWITHSUNDEEP
excelvbapivotpivot-table
Karlo Kraljic
Karlo Kraljic

Reputation: 173

Create Pivot from already existing PivotCache

I want to create a new PivotTable based on the data that was used to create another PivotTable in another workbook.

I've realized the following stuff: 1.) Open the Workbook that holds data 2.) Copy the worksheet containing the PivotTable to the new Workbook

And now I want to access the Cache from the existing PivotTable and create a new one on a different Worksheet in the same Workbook. Therefore I'm using the following code: Set input_pivot_sheet = input_workbook.Worksheets("Worksheetbblabla")

'Select right Pivot Table
Set pivot_table = input_pivot_sheet.PivotTables(2)

'Create new Excel file
Set temp_excel_workbook = Workbooks.Add
Application.SheetsInNewWorkbook = 1

'Create supportive Pivot by copying content from old file to new file
input_pivot_sheet.Copy After:=temp_excel_workbook.Sheets(1)
Set pivot_cache = pivot_table.PivotCache
'Create new Pivot out of this pivot...
Set worksheet_1 = temp_excel_workbook.Sheets(1)
new_pivot_table = pivot_cache.CreatePivotTable(worksheet_1.Range("A1"))

This code is failing since I get an Run-time error 5: Invalid procedure call or argument in this line: 

new_pivot_table = pivot_cache.CreatePivotTable(worksheet_1.Range("A1"))

How can I access the data from the other PivotTable and plot a new PivotTable on another worksheet?

Upvotes: 0

Views: 1124

Answers (1)

Karlo Kraljic
Karlo Kraljic

Reputation: 173

Making use of cyboashu's comment (you can find it as a comment at the question post) I adapted my code a little bit:

     'Switch to right Worksheet - Attention if "blablabka" is renamed...!!!!
    Set input_pivot_sheet = input_workbook.Worksheets("blabla")

    'Create new Excel file
    Set temp_excel_workbook = Workbooks.Add
    Application.SheetsInNewWorkbook = 1

    'Create supportive Pivot by copying content from old file to new file
    input_pivot_sheet.Copy After:=temp_excel_workbook.Sheets(1)

    'Close old file & newly created one
    input_workbook.Close
    temp_excel_workbook.SaveAs Filename:=temp_excel_file_name
    temp_excel_workbook.Close

    'Open new Excel... - not performant...
    Set temp_excel_workbook = Workbooks.Open(temp_excel_file_name)

    'Select right Pivot Table
    Set pivot_table = temp_excel_workbook.Sheets(2).PivotTables(2)
    Set pivot_cache = pivot_table.PivotCache
    'Create new Pivot out of this pivot...
    Set worksheet_1 = temp_excel_workbook.Sheets(1)
    new_pivot_table = pivot_cache.CreatePivotTable(worksheet_1.Range("A1"))

Now I can add the fields and rows. Thank you all!

Upvotes: 1

Related Questions