Tabellenblätter kopieren Formelbezüge automatisch anpassen Excel 2016

[Naddus0207]

Hallo,
 
ich habe eine Datei mit drei Tabellenblättern (PDF_1; Zert_D; Übertrag).

Diese kopiere ich via CommandButton mithilfe folgender Formel:

Private Sub CommandButton1_Click()
Sheets(Array("PDF_1", "Zert_D", "Übertrag")).Select
Sheets("Zert_D").Activate
Range("O2") = Range("O2") + 1
Range("P2") = Range("P2") + 1
Range("Q2") = Range("Q2") + 1
ActiveSheet.Copy After:=Sheets(ThisWorkbook.Sheets.Count)
ActiveSheet.OLEObjects("CommandButton1").Delete
ActiveSheet.Name = "Zert_D" & Sheets("Zert_D").Range("O2").Text
Sheets("Übertrag").Activate
ActiveSheet.Copy After:=Sheets(ThisWorkbook.Sheets.Count)
ActiveSheet.Name = "Übertrag" & Sheets("Zert_D").Range("P2").Text
Sheets("PDF_1").Activate
ActiveSheet.Copy After:=Sheets(ThisWorkbook.Sheets.Count)
ActiveSheet.Name = "PDF_" & Sheets("Zert_D").Range("Q2").Text
End Sub

Die Formel funktioniert auch wunderbar, auch das Umbenennen klappt 1A.

Nun mein Anliegen:
Im Tabellenblatt PDF_1 haben die Formeln Bezüge zu den Tabellenblättern Übertrag und Zert_D.
Wie kann ich meine Formel jetzt anpassen, so dass sich die Formeln im kopierten Tabellenblatt PDF_2 auf die kopierten Tabellenblätter Übertrag2 und Zert_D2 beziehen? Und wie bekomme ich das hin, dass das Ganze auch bei 40 x koopieren klappt?

Danke.

[Gast 123]

Hallo

normalerweise arbeite ich nicht mit Select, hier scheint es aber am einfachsten zu sein.
Überrascht wie einfach man den Code mit Index erweitern kann??  Probier ihn bitte aus.

mfg Gast 123

Code:

Private Sub CommandButton1_Click()
Dim PDFX As Worksheet, a, b, c
Sheets(Array("PDF_1", "Zert_D", "Übertrag")).Select
Sheets("Zert_D").Activate
Range("O2") = Range("O2") + 1
Range("P2") = Range("P2") + 1
Range("Q2") = Range("Q2") + 1
a = Range("O2")   'Index für ZertD
b = Range("P2")   'Index für Übertrag
c = Range("Q2")   'Index für PDF
ActiveSheet.Copy After:=Sheets(ThisWorkbook.Sheets.Count)
ActiveSheet.OLEObjects("CommandButton1").Delete
ActiveSheet.Name = "Zert_D" & a
Sheets("Übertrag").Activate
ActiveSheet.Copy After:=Sheets(ThisWorkbook.Sheets.Count)
ActiveSheet.Name = "Übertrag" & b
Sheets("PDF_1").Activate
ActiveSheet.Copy After:=Sheets(ThisWorkbook.Sheets.Count)
ActiveSheet.Name = "PDF_" & c
Cells.Replace "Zert_D", "Zert_D" & a
Cells.Replace "Übertrag", "Übertrag" & b
End Sub

[Naddus0207]

Hallo,

danke schon einmal. Dein Code kopiert aber nur Zert_D und benennt das dann auch nicht fortlaufend, also Zert_D2, Zert_D3, sondern Zert_D (2), Zert_D (3). Die anderen beiden Tabellenblätter werden gar nicht kopiert.
(das passiert, wenn mann nur die Hälfte kopiert )

der Code kopiert zwar die Blätter und benennt auch die Blätter richtig, aber die Zellbezüge in den Formeln aktualisieren sich trotzdem nicht.

---

Anbei mal die Datei.

[Kuwer]

Hallo,

Code:

Private Sub CommandButton1_Click()
  With Sheets("Zert_D")
    .Range("O2").Value = .Range("O2").Value + 1
    .Range("P2").Value = .Range("P2").Value + 1
    .Range("Q2").Value = .Range("Q2").Value + 1
    Sheets(Array("PDF_1", "Zert_D", "Übertrag")).Copy After:=Sheets(Sheets.Count)
    Sheets(Sheets.Count - 2).Name = "PDF_" & .Range("Q2").Text
    Sheets(Sheets.Count - 1).Name = "Zert_D" & .Range("O2").Text
    Sheets(Sheets.Count - 0).Name = "Übertrag" & .Range("P2").Text
  End With
  Sheets(Sheets.Count - 1).OLEObjects("CommandButton1").Delete
End Sub

Gruß, Uwe

[Naddus0207]

Vielen Dank, funktioniert perfekt.

[Gast 123]

Hallo

ich musste deine Datei leider mit LibreOffice in Excel 2003 formatieren, dabei gehen alle UserForm Elemente und alle Makros verloren.
Setze ich meinen Code in das Beispiel ein bekomme ich im Blatt PDF_2 in den Zellen A23, A27 und A36 eindeutig diese Formel angezeigt:
A36 = =Zert_D2!J2
A23 = =Übertrag2!B1&" "&Übertrag2!C1,  
A27 = ="vom"&" "&TEXT(Übertrag2!B3;"TT.MM.JJJJ")&" "&"bis"&" "&TEXT(Übertrag2!C3;"TT.MM.JJJJ")
Einen Laufzeitfehler habe ich bei:  ActiveSheet.OLEObjects("CommandButton1").Delete   Das klappt bei mir nur mit DrawingObjects!

@Uwe   bei dir fehlt noch im Code die Änderung der Formeln auf di kopierten Sheets!

mfg Gast 123

[Naddus0207]

@Uwe   bei dir fehlt noch im Code die Änderung der Formeln auf di kopierten Sheets!

Das Ändern der Formeln klappt perfekt.

[Gast 123]

Korrigiere mich und Frage an Uwe:  ändern sich durch die Arrayfunktion die Formeln automatisch??  Wusste ich nicht.

[Kuwer]

Frage an Uwe:  ändern sich durch die Arrayfunktion die Formeln automatisch??

Durch das gemeinsame Kopieren der voneinander abhängigen Sheets gehen die Bezüge mit.

Gruß, Uwe

[Naddus0207]

Eine Frage hätte ich noch, kann man die Formel so modifizieren, das alle 3 Blätter auf einmal kopiert werden, dann aber nicht an das Ende gesetzt werden, sondern hinter das entsprechende Blatt? Also PDF_2 hinter PDF_; PDF_3 hinter PDF_2, usw..