VBA Copy Worksheets, Changing Pivot Cache, Run Time Error 5

[Dichnich]

Hi helping folks and gods of coding.

As a newbee in vba codes without any deeper knowledge I check forums like this to find answers to what I have done wrong. But for this problem I have not found a fittig solution or at least any I could crasp. So I have to ask dirctly and hope for helpfull answers.

ATM I'm working on a vba code to copy multiple sheets containing pivot charts/tables with and without data slicers as well as their data source to a new workbook.

All pivot share the same data source organised as a table (Listobject). That code worked fine until I discovered that the pivots still refered to the original workbook.

I searched the internet and found at TheSpreadsheetGuru.com a solution to change the PivotCache via vba. That code on itself worked fine too, if run directly in the new workbook. So i tried to implement the changing of the PivotCache into the code to copy the sheets to the new worksheet. The result was folowing code:

Private Sub cmdTest_Click()
' Copy worksheets and change PivotCache
'SOURCE: TheSpreadsheetGuru/The-Code-Vault (part of code for PivotCache change)

' Variables for loop

Dim wb As Workbook
Dim ws As Worksheet
Dim pt As PivotTable

' Variables for changing PivotCache

Dim Data_sht As Worksheet
Dim Pivot_sht As Worksheet
Dim PivotName As String
Dim NewRange As String
Dim Source As String

' Variables for filename and path

Dim Path As String
Dim NewName As String

Path = ActiveWorkbook.Path
NewName = "HH_Plan " & Worksheets("Steuerungselemente").Range("D5").Value & "_" & _
Worksheets("Steuerungselemente").Range("D7").Value & " Diagramme.xlsx"
'MsgBox ("name: " & Path & NewName)

' Copy Sheets to new Workbook

Worksheets(Array("Gesamthaushalt", "Teilhaushalt", "Planansätze", "Stammdaten")).Copy

' Set Variables for PivotCache

Set Data_sht = ThisWorkbook.Worksheets("Planansätze") 'Sheet with data source
Source = "tbl_planansatz" ' Data Source (= Table / ListObject)
NewRange = Data_sht.Name & "!" & Source ' New range for PivotCache

' Loop for PivotCache change and value format

Set wb = ActiveWorkbook
For Each ws In wb.Sheets
For Each pt In ws.PivotTables
With pt
.ChangePivotCache ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=NewRange)
.PivotCache.Refresh
For Each pf In .DataFields
pf.NumberFormat = "#,##0 €"
Next pf
.ColumnRange.HorizontalAlignment = xlCenter
.PivotCache.Refresh
End With
Next pt
Next ws

' Loop for sheet protection (inactive for testing)

'For Each ws In wb.Sheets
' With ws
' .Protect Password:="xxx", DrawingObjects:=False, Contents:=True, Scenarios:= _
' False, AllowFiltering:=True, AllowUsingPivotTables:=True
' End With
'Next ws

' Hide Source Data sheets and workbook protection. Save to Filename and path

Worksheets(Array("Planansätze", "Stammdaten")).Visible = False
Worksheets("Gesamthaushalt").Activate
ActiveWorkbook.ShowPivotTableFieldList = False
'ActiveWorkbook.Protect Password:="xxx", Structure:=True, Windows:=False
ActiveWorkbook.SaveAs Filename:=Path & "\" & NewName
End Sub

Running the code results in a Runtime Error 5: Invalid Procedure Call or Argument statement when hitting the line

.ChangePivotCache ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=NewRange)

As running the code for copying without changing the PivotCache and running the PivotCache change after copying the sheets doesn't result in an error, the run-time error must be connected to the combination of both steps. I guess the changing of the PivotCache can't be done in this state of the new workbook, but I cant think of any workaround to a problem I don't understand. So I musst surrender to excel and vba.

As perhaps the one or the other might guess from the names of the sheets I normaly communicate in german . So please excuse my partly broken and clumsy attemps or outright assaults on english language and gramma. But I hope that you can help me nontheless.

Thanks in advance and kind regards

[CheeseSandwich]

I think this:

Please Login or Register  to view this content.

Needs to be:

Please Login or Register  to view this content.

ThisWorkbook is always the workbook where the code resides.
ActiveWorkbook is the active workbook regardless of where the code resides.
wb in this instance has been defined as ActiveWorkbook so we should use this.

[CheeseSandwich]

You may also need to change:

Please Login or Register  to view this content.

To:

Please Login or Register  to view this content.

Hope this helps

[Dichnich]

I think this:

Please Login or Register  to view this content.

Needs to be:

Please Login or Register  to view this content.

ThisWorkbook is always the workbook where the code resides.
ActiveWorkbook is the active workbook regardless of where the code resides.
wb in this instance has been defined as ActiveWorkbook so we should use this.

Thanks

Please Login or Register  to view this content.

helped.

I allready thoght that it might be something "small" and "very basic".

The Structure of a code is something I might crasp and I can often tinker my way to a working code. But I really lack gramma. Time for taking some VBA training lessions :-)

Again, thanks for the awesome and fast help

Kind Regards