Office: (Office 365) Tabellenblätter aus Vorlagen-Tabellenblatt generieren mit dynamischen Anpassungen durch Vorgaben

Hallo,

ich suche gerade eine Möglichkeit mittels VBA mehrere Tabellenblätter auf einmal zu generieren (ca. 40), die auf einem Vorlage-Tabellenblatt (TB2) basieren. Das kopieren/ Vervielfachen an sich wäre noch recht easy, nur möchte ich in den Tabellenblättern gewisse Zellen (z.B. C3) abhängig von einer weiteren Tabelle in Tabellenblatt 1 (TB1) machen und wenn es geht auch die jeweiligen TB-Blatt Namen danach benennen.

Also quasi folgendes Szenario:

TB1: Tabelle mit Überschriften

A1=Überschrift1
A2=Überschrift2
An=Überschriftn etc.
​

TB2: grafische Vorlage mit fixen Werten und Texten, die vervielfältigt werden sollen

TB3: TB-Name und Zelle C3 mit Überschrift1
TB4: TB-Name und Zelle C3 mit Überschrift2
... bis TB42
​

Gibt es da eine Möglichkeit? Am liebsten wäre es mir die beiden Dinge im Code zu trennen, also erst das Anlegen der Tabellenblätter, dann das benennen der Tabellenblätter und dann erst das Einfügen der Überschrift in C3


Hintergrund: Eigentlich möchte ich eine Vorlage vervielfältigen, für mehrere Projekte, diese dann als einzelne Dateien abspeichern und Quartalsweise mit diesen Dateien Statusabfragen machen, die ich dann in einer Übersichts-Arbeitsmappe automatisiert einlese und zusammenfasse.

 

Hi,

da du geschrieben hast, dass das Erstellen der Blätter kein Problem sei - hier eine Möglichkeit zum nachtäglichen Benennen der Blätter und Eintrag in C3 (Code ungetestet):

Code:

Sub TabsBenennen()
    Dim wksTab As Worksheet
    Dim lngZeile As Long
    lngZeile = 1
    For Each wksTab In ThisWorkbook.Worksheets
        If wksTab.Name <> "TB1" And wksTab.Name <> "TB2" Then
            wksTab.Name = Worksheets("TB1").Cells(lngZeile, 1)
            wksTab.Range("C3") = Worksheets("TB1").Cells(lngZeile, 1)
            lngZeile = lngZeile + 1
        End If
    Next wksTab
End Sub

---

 

Hi Beverly und danke für die schnelle Antwort!
Also als code zum Kopieren nehme ich einfach folgendes:

Code:

Sub TabsErstellen()
    Dim intZaehler As Integer
    For intZaehler = 1 To 40
        Worksheets("I1").Copy after:=Worksheets(Worksheets.Count)
        Worksheets(Worksheets.Count).Name = intZaehler + 1
    Next intZaehler
End Sub

Zu deinem Code: Ich habe A1-n nur als Beispiel genommen, so wie ich den Code verstehe geht LngZeile auf genau die erste Spalte ein, oder? Ist es auch möglich die "Startzelle", wo es dann mit den weiteren Zeilen in der Spalte weitergehen soll genau zu definieren?

 

lngZeile = 1
ist die "Beginnzeile", wenn du erst ab Zeile 5 anfangen willst, setze hier
lngZeile = 5

 

Hi Diza, danke!
Und wie beginne ich mit einer Beginnzeile 5 aber in Spalte D?

 

Die Zelladresse wird folgendermaßen dargestellt: Cells(Zeilennummer, Spaltennummer)

---

 

Ah nice, danke! :) dann versuche ich das nachher mal und melde mich, ob es so geklappt hat!

 

ich habe jetzt folgenden Code als Modul eingefügt:

Code:

Option Explicit

'Tabellenblätter werden nach den Zellen ab Zeile 06 fortlaufend durchbenannt und auch die Zelle (Zeile, Spalte(als Ziffer)) wird in dem jeweiligen Tabellenblatt so benannt (Ab C6 in TB: Projekte)
Sub TabsBenennen()
    Dim wksTab As Worksheet
    Dim lngZeile As Long
    'lngZeile bedeutet Start ab Zeile 06
    lngZeile = 6
    For Each wksTab In ThisWorkbook.Worksheets
        If wksTab.Name <> "Gesamtübersicht" And wksTab.Name <> "Projektübersicht" And wksTab.Name <> "B1" And wksTab.Name <> "Projekte" Then
            wksTab.Name = Worksheets("Projekte").Cells(lngZeile, 3)
            wksTab.Range("K6") = Worksheets("Projekte").Cells(lngZeile, 3)
            lngZeile = lngZeile + 1
        End If
    Next wksTab
End Sub

Die Namen der Tabellenblätter, die verändert werden sollen sind die Ziffern 1-40

es kommt jedoch folgende Fehlermeldung:
Laufzeitfehler '1004':
Der sicher eingegebene Name für ein Blatt oder Diagramm ist ungültig. Stellen sie folgendes Sicher:

• eingegebener Name max. 31 Zeichen
• Name enthält keine Sonderzeichen
• es wurde ein Name eingegeben

 

Was genau steht denn in der laufenden Zelle in Spalte C?

---

 

Hi Beverly, der Hinweis war super, hätte ich natürlich auch selbst drauf kommen können. In Spalte C habe ich die Projektnummer und den Projektnamen mit einer Formel zusammengefasst. Deshalb möchte ich nun in der Zelle K6 von den TB1-40) die C6+ Werte und als Tabellennamen eine Zusammensetzung aus Text und laufender Projektnummer (also A6+).

habe den Code angepasst und habe danach noch diese Fehlermeldung bekommen:
Laufzeitfehler '9': Index außerhalb des gültigen Bereichs

Ich hatte den Eindruck, dass ich noch definieren muss, wie viele Tabellenblätter so benannt werden sollen. Bin dann den umgekehrten Weg gegangen und habe genau so viele Tabellenblätter nach den ersten 4 gelassen, wie viele Zeilen/Projekte es gibt (38 statt 40) Ich dachte der Code geht alle Tabellenblätter durch und die letzten benennt er einfach nicht um bzw. wenn es keine weiteren Tabellenblätter mehr gibt hat er eben keine weiteren TB mehr, um die übrigen Zeilen einzufügen.

Was auch komisch war, dass die referenzierten Werte in der Tabelle im TB "Projekte" wie gesagt ab Zeile 6 beginnen, als ich den Code mit lngZeile = 6 durchlaufen lassen habe, war aber alles um 2 verschoben und die TB begannen erst ab 3 zu zählen. Deshalb habe ich lngZeile auf 4 gesetzt, so funktioniert es nun und es wird ab Projekt 1 gezählt, verstehe ich jedoch nicht so recht.

Nunja. mit der gleichen Anzahl an TB, die der Anzahl der folgenden Zeilen entspricht funktioniert jetzt der folgende Code ohne Fehlermeldung:

Code:

Option Explicit

'Tabellenblätter werden nach den Zellen ab Zeile 06 fortlaufend durchbenannt und auch die Zelle (Zeile, Spalte(als Ziffer)) wird in dem jeweiligen Tabellenblatt so benannt (Ab C6 in TB: Projekte) (ab A6 ist die laufende Projektnummer)
Sub TabsBenennen()
Dim wksTab As Worksheet
Dim lngZeile As Long
    'lngZeile bedeutet Start ab Zeile 06
    lngZeile = 4
    For Each wksTab In ThisWorkbook.Worksheets
        If wksTab.Name <> "Gesamtübersicht" And wksTab.Name <> "Projektübersicht" And wksTab.Name <> "B1" And wksTab.Name <> "Projekte" Then
        'Tabellenblattname soll wie foglt sein: "Proj. Nr. XX+XX - IP PV"
            wksTab.Name = "Proj. NR. " & Worksheets("Projekte").Cells(lngZeile, 1) & " - IP PV"
        'In Zelle K6 von den zu vervielfältigenden Inputblättern sollen die Zellen C6 ff. in die jeweiligen Tabellenblätter fortlaufend übernommen werden
            wksTab.Range("K6") = Worksheets("Projekte").Cells(lngZeile, 3)
            lngZeile = lngZeile + 1
        End If
    Next wksTab
End Sub

 

Den Grund für das Problem mit der Zeilennummer kann ich - ohne die Arbeitsmappe zu sehen - so nicht nachvollziehen. In meiner Beispielmappe hat der Code jedenfalls korrekt funktioniert. Er läuft ja über alle in der Mappe existierenden Tabellenblätter (unabhängig von der Gesamtanzahl!) und wenn deren Name weder "Gesamtübersicht", noch "Projektübersicht", noch "B1" noch "Projekte" ist (also die Namen den neu erstellten Blättern entsprechen) soll der Code in die If-Verzweigung gehen und das laufende Blatt entsprechend der laufenden Zeile benennen und außerdem den Namen in die Zelle K6 eintragen.

Ich kann auch nichts Konkretes zu der Fehlermeldung Laufzeitfehler '9': Index außerhalb des gültigen Bereichs sagen, da du nicht geschrieben hast, in welcher Codezeile sie auftritt. Das Einzige was ich dazu sagen kann ist, dass er immer dann auftritt, wenn das angesprochene Objekt nicht vorhanden ist.

---