Office: (Office 2013) VBA Änderung der PIVOT Datenquelle nach Muster-Tabellenblatt-Kopie

Hallo zusammen,
ich suche in diesem Forum Unterstützung, da ich ein der VBA Programmierung in einem Punkt nicht weiter komme.
Hintergrund/Aufbau:
Ich habe zur Bearbeitung für mehrere Anwender gleichzeitig eine Exceldatei erschaffen, welche in einem Tabellenblatt einzutragene Felder für jeweils eine Kalenderwoche bietet. Diese Eintragefelder werden mit Hilfe von 6 Pivot Tabellen und deren Diagramme mit jeweils 6 einzelnen Datenquellentabellen innerhalb des Muster-Tabellenblattes ausgewertet.
Nun ist das Muster-Tabellenblatt fertig. Nun soll durch ein Makro weitere Tabellenblätter nach dem Muster für gezielte Kalenderwochen erstellt werden. Bis hier hin kein Problem.
In dem neu erstellten Tabellenblatt mit dem Tabellenblattnamen der jeweiligen Kalenderwoche beziehen sich nun aber die 6 Pivot Tabellen und deren Diagramme immer mit der Datenquelle auf die Tabellen der Muster-Vorlage.
Wie kann ich per VBA den Datenbereich, der sich im neuen Tabellenblatt lediglich im Tabellenblattnamen ändert, umschreiben ?

Ich habe nun schon zwei Tage vergeblich im Netz nach passenden Beispielen gesucht und erhoffe mir hier in diesem Forum praktische Hilfe.
Ich hoffe, dass ich mein Problem ausreichend geschildert habe.
Bei Bedarf kann ich die Problematik tiefgründiger schildern.

Vielen Dank vorab.
Robert

 

Sub ZweiBlaetterMitPivotUndGeaenderterDatenquelle()

'neues Workbook mit nur einem Blatt (dt. "Tabelle1") anlegen
'Daten in A1:B4 eintragen
Workbooks.Add xlWorksheet
[A1:B1] = Split("A B"): [A2:B2] = Array(1, 1): [A3:B3] = Array(1, 2): [A4:B4] = Array(5, 4)

'Erste Pivot-Tabelle in D1 erzeugen und bestimmen
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Tabelle1!R1C1:R4C2", Version:=xlPivotTableVersion14).CreatePivotTable _
TableDestination:="Tabelle1!R1C4", TableName:="PivotTable1", _
DefaultVersion:=xlPivotTableVersion14
ActiveSheet.PivotTables("PivotTable1").PivotFields("A").Orientation = xlRowField
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("B"), "Summe von B", xlSum

'Zweite Pivot-Tabelle in H1 erzeugen und bestimmen
ActiveWorkbook.Worksheets("Tabelle1").PivotTables("PivotTable1").PivotCache. _
CreatePivotTable TableDestination:="Tabelle1!R1C8", TableName:= _
"PivotTable2", DefaultVersion:=xlPivotTableVersion14
ActiveSheet.PivotTables("PivotTable2").PivotFields("A").Orientation = xlColumnField
ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _
"PivotTable2").PivotFields("B"), "Summe von B", xlSum

'eine neue Tabelle aus Tabelle1 kopieren (inkl. der beiden Pivot-Tabellen)
'diese beziehen sich jedoch ohne einen Eingriff weiterhin auf Datenquelle Tabelle1
Sheets("Tabelle1").Copy After:=Sheets(1)

'Anpassung eines Wertes der Datenquelle auf dem neuen Blatt (zur Kontrolle)
[B4] = 6

'manuelle Änderung der Datenquelle von PT1/neu auf die des neuen Blatts
'Die Datenquelle von PT2/neu hingegen verbleibt auf dem alten Blatt!
ActiveSheet.PivotTables("PivotTable1").ChangePivotCache ActiveWorkbook. _
PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Tabelle1 (2)!R1C1:R4C2", Version:=xlPivotTableVersion14)

'von den 4 PT nutzen nun 3 die alte Datenquelle; nur eine die neue!
'Die 3 blauen Befehle nacheinander:
'- Neue PT mit neuem Cache
'- Neue PT mit bestehendem Cache
'- Bestehende PT mit neuem (bzw. geändertem) Cache
'Seltsam: Der Cache bekommt keinen Namen.

End Sub

Nicht geprüft habe ich angesichts der Namenlosigkeit des Caches, ob ein erneutes Zuweisen des alten Caches zu insgesamt 2 oder 3 Caches führt. Die Antwort ließe sich jedoch bei großen Datenquellen anhand der Dateigröße ermitteln. Den Bereich, auf den sich der Cache bezieht, kann man natürlich benennen. Das aber ist dann ein Bereichsname. Einen Cachenamen scheint es hingegen nicht zu geben. Oder? (im Eigenschaftenfenster nachforschen)

 

Hallo lupo1,

vielen Dank für Deine Antwort.
Der part: ChangePivot.Cache funktioniert leider nicht.
Das neu erzeugte Tabellenblatt hat sofort nach Erstellung einen bestimmten Namen. In dem Fall die jeweilige Kalenderwoche( z.B. 2, 3, 5, 23 , ...)
Wie schaffe ich es, dass eben dieser Tabellenname in der SourceData eingeschrieben wird.
Bei Deinem Beispiel: Tabelle1(2) kommt eine Fehlermeldung, dass die Pivot Quelldatei nicht geöffnet werden kann. Ok, existiert auch nicht.
Wenn ich aber nun den tatsächlichen Tabellennamen in die SourceData schreibe, kommt die Fehlermeldung, die PivotTables Eigenschaft des Worksheet-Objektes kann nicht zugeordnet werden.

Auf Deine Frage mit dem Cachenamen, was im Eigenschaftenfenster zu prüfen wäre, komme ich inhaltlich auch nicht weiter.
Sorry.
Irgendwie ist mir die Erstellung der Quelldaten über eine VBA ein Rätsel.
Ich kenne das Tabellenblatt (aktives) und den jeweiligen Zellbereich in einem Range. Dennoch gelingt mir der Quark nicht, diese festen Werte in so eine Anweisung zu schreiben.

Bisher waren die Befehle über ein längeres Selbststudium selbst erklärend und irgendwann auch logisch anwendbar.

Hilft es , wenn ich eine Beispieldatei erstelle ?

Vielen Dank nochmal vorab.
Robert