[Excel mVBA] Zellbezüge nach Kopieren & Umbennen anpassen

[Skibi]

Hi ihr Profis,

ich habe über die Suchfunktion leider nichts finden können. Vielleicht weil ich hier auch falsche Begrifflichkeiten nutze. Ich hätte einen Usecase, bei dem ich leider nicht weiterkomme und ich hoffe, dass ihr mir weiterhelfen könnt.

Zusammensetzung des Workbooks:
1. Deckblatt
2. Vorlage_TabelleX (Normalerweise ausgeblendet)
3. Vorlage_TabelleY (Normalerweise ausgeblendet)

1.Deckblatt: Hier befinden sich 1 Button um die "Vorlage_TabelleX" und die "Vorlage_TabelleY" zu kopieren, sichtbar zu machen und diese in "NeuerTabellenNameX"+i und "NeuerTabellenNameY"+i umzubenennen.
 i steht hierbei für die Anzahl der aktuell vorhandenen Tabellenblätter mit den Namen "NeuerTabellenNameX" und "NeuerTabellenNameY".

2.) Ich kann mir nun also mit einem Klick auf den Button beliebig viele Päckle hinzufügen und sie werden schön sauber beschriftet. Nun zu dem eigentlichen Problem:
Step A) In TabelleX sind in der Spalte B Zahlen eingetragen, diese werden in der Regel vom User eingetragen.
Step B) Diese Daten sollen in Tabelle Y weiter verwendet werden (in unserem Beispiel wieder in Spalte B) 

Problem: Ich habe die Zellbezüge in der Vorlage erstellt (Beispiel: "=Vorlage_TabelleX!B2") Wenn ich die Blätter nun kopiere und umbenenne ändert sich der Zellbezug natürlich nicht mit, woher soll Excel das auch wissen. 

Gibt es hier eine Möglichkeit mit Variablen zu arbeiten und sich die Zellen automatisch aktualisieren zu lassen?

Ich dachte mir ich könnte ja den Endnamen bereits verknüpfen es müsste sich ja nur die Zahl in der Zelle ändern. 
Beispiel: Der Zellbezug in unserer neu erstellten Tabelle "NeuerTabellenNameY1" in der Zelle B2 bezieht sich nach dem Kopieren auf "=Vorlage_TabelleX!B2" 
Ich könnte den Bezug der Vorlage ja ins leere laufen lassen "=NeuerTabellenNameX1!B2" somit wäre nach dem Kopieren der Bezug eigentlich stimmig, müsste aber noch aktualisiert werden (Aktivieren der Zelle und bestätigen). 
Wenn nun aber ein weiterer Datensatz hinzu gefügt wird wäre die Zahl im Zellenbezug (in unserem Fall: "=neuerTabellenNameX1!B2" ) natürlich falsch! Könnte man diese Zahl über eine Variable mit einer Abfrage ändern?


Ich merke beim schreiben wie unverständlich das wirkt, ich hoffe ihr könnt das iwie nachvollziehen, was ich damit erreichen will. 
Ich habe die Beispieldatei ins Attachment gehängt und hier noch der Makrocode:

Sub Tabellenblattkopieren()
'Sheets Kopieren
    Sheets("Vorlage_TabelleX").Copy After:=ThisWorkbook.Sheets(Sheets.Count)
    Sheets("Vorlage_TabelleY").Copy After:=ThisWorkbook.Sheets(Sheets.Count)
'Sheets Sichtbar machen
    Sheets("Vorlage_TabelleX (2)").Visible = True
    Sheets("Vorlage_TabelleY (2)").Visible = True
'Namen aendern
    Sheets("Vorlage_TabelleX (2)").Name = nextFreeBlattname(ThisWorkbook)
    Sheets("Vorlage_TabelleY (2)").Name = nextFreeBlattname2(ThisWorkbook)
'Springe zum Deckblatt zurück
    Sheets("Deckblatt").Activate
        
End Sub

Function nextFreeBlattname(ByRef wb As Workbook)
    Dim blattRoot As String
    Dim i As Integer
    Dim vorhanden As Boolean
    Dim ws As Variant
    
    blattRoot = "NeuerTabellenNameX" ' Die Wurzel aller Blattnamen ohne Nummerierung
    
    For i = 1 To 10000
        vorhanden = False
        For Each ws In wb.Worksheets
            If ws.Name = blattRoot & i Then vorhanden = True
        Next ws
        If Not vorhanden Then Exit For
    Next i
    nextFreeBlattname = blattRoot & i
    
End Function

Function nextFreeBlattname2(ByRef wb As Workbook)
    Dim blattRoot As String
    Dim i As Integer
    Dim vorhanden As Boolean
    Dim ws As Variant
    
    blattRoot = "NeuerTabellenNameY" ' Die Wurzel aller Blattnamen ohne Nummerierung
    
    For i = 1 To 10000
        vorhanden = False
        For Each ws In wb.Worksheets
            If ws.Name = blattRoot & i Then vorhanden = True
        Next ws
        If Not vorhanden Then Exit For
    Next i
    nextFreeBlattname2 = blattRoot & i
    
End Function

Fall mir hier jemand weiterhelfen kann wäre das super! Ich dreh hier mittlerweile ganz schön am Rädle :16:  und bin was Excel angeht eigentlich ziemlich unbedarft. 

Grüßle Skibi

[Bast4i]

Hi Skibi.

Also mein Rat wäre, auch die Formeln in VBA auszulagern. Da kann man das Ganze viel besser handeln.
Ich kann mir aber vorstellen, dass es auch mit Formeln geht. Ist zwar unschön und nur was für Leidensfähige, aber möglich.

Mit

Code:

=ZELLE("dateiname")

bekommst Du den Pfad (!!!!!) inkl. Blattnamen zurück.

Da jedes Blatt X i ja weiß, welches BlattY i zu ihm gehört, nämlich das mit demselben i, kannst Du mit der obigen Formel den Blattnamen des jeweiligen Blattes ermitteln, und entsprechend auf seinen Partner referenzieren.
Zum Ermitteln des Blattnamens den Pfad am besten auf "]" durchsuchen, danach folgt dann der beliebig lange Blattname. Das ist besser als eine bestimmte Zeichenzahl von rechts nach links zu gehen, da Du beim Wechsel von Blatt X 9 auf Blatt X 10 eine Stelle mehr hast. Es sein denn Du schreibst Blatt X 09. Dann wäre es egal. Aber das weißt Du am besten selbst.

Gruß
Sebastian

P.S.: Poste mal eine fertige Formel. Das ist bestimmt was fürs Museum!

[Skibi]

Ein Kollege von mir hat eine andere Möglichkeit gefunden, wie die Bezüge automatisch übernommen und angepasst werden. 

Man muss hierzu die Copy Befehle zu einem array zusammenfassen:

 Sheets(Array("Vorlage_TabelleX", "Vorlage_TabelleY")).Copy After:=ThisWorkbook.Sheets(Sheets.Count)

Das Visible machen und das Umbenennen erfolgt dann wie bisher auch.