Pivot Table

[Joe]

Hi Dear

Please, I need your help.

I'm trying to create a PivotTable based in another PivotTable in the same
Workbook.

This is the code:

Sub Teste()
Dim wks As Worksheet
Dim wks1 As Worksheet
Dim rng As Range
Dim rng1 As Range
Dim pvt As PivotTable
Dim pvt1 As PivotTable

Set wks = wksReport
Set wks1 = wksData
Set rng = wks.Range("C2")
Set pvt1 = wks1.PivotTables(1)

Set pvt = wks.PivotTableWizard(xlpivotttable, pvt1, rng, "MyDinTable",
True, True)

' ...
End Sub

Running the Sub occurs the error: 1004 - The PivotTable methode has failed!

What is the problem? Where can I find a solution?

Thanks in advance.

Joe

 

[SeanC UK]

Hi Joe,

Try using the PivotCache rather than the table.


Dim shtOrig As Worksheet
Dim pvtOrig As PivotTable
Dim shtNew As Worksheet
Dim pvtNew As PivotTable
Dim pvc As PivotCache
Set shtOrig = ThisWorkbook.Worksheets(1)
Set shtNew = ThisWorkbook.Worksheets(2)
Set pvtOrig = shtOrig.PivotTables(1)
Set pvc = pvtOrig.PivotCache
Set pvtNew = pvc.CreatePivotTable(shtNew.Range("A1"), "My New Pivot")


Sean.