Mehrere Tabellenblätter in neue Datei verschieben
28.03.2019 21:27:42
Richard
ich versuche unten beschriebene Aufgabe zu lösen. Eigentlich dachte ich, ganz einfach. Alles irgendwie schon mal gemacht. Für jeden Teilschritt habe ich Lösungen, die für sich funktionieren. Aber die Kombination der Teilschritte bekomme ich nicht in den Griff. Dabei habe ich verschiedene Varianten ausprobiert. Es kommen bei jeder Variante Fehler, die ich nicht verstehe.
Ich habe eine Datei (wkbQuelle) mit vielen Auswertungen = Tabellenblättern. Eine Auswahl dieser Auswertungen (zunächst 4 Tabellenblätter; wksTemp) möchte ich zusammen in einer neuen Datei (wkbZiel) zusammenfassen (und per Email verteilen).
Dabei sollen nur die Formate und die Werte übernommen werden, da ich in der verteilten Datei keine untereinander verknüpften Excel-Dateien auf dem Server haben möchte. Die Namen der Tabellenblätter sollen übernommen werden.
In diversen Anläufen habe ich entweder verschiedene Laufzeitfehler erhalten oder, bestes Ergebnis, 4 Dateien mit jeweils einem der Arbeitsblätter.
Schließlich war meine Idee über .UsedRange und .Range die Daten in die neue Tabelle zu verschieben. Aber ich erhalte wieder einen Laufzeitfehler 1004, weil die PasteSpecial-Methode des Range-Objektes nicht ausgeführt werden konnte. Bitte?
Wenn ich zu Testzwecken diese Zeile entferne, bekomme ich bei der Kopie des Namens einen Laufzeitfehler, weil Excel nicht einem Blatt den gleichen Namen geben wie einem anderen Blatt kann, auf die Visual-Basic Bezug nimmt. Aber diese Vorgehensweise habe ich für das Verschieben von EINEM Tabellenblatt in eine neue Datei funktionsfähig gesehen!? Und warum sollten in zwei Dateien die Tabellenblätter nicht gleiche Namen haben dürfen?
Wenn ich wieder zu Testzwecken auch diese Zeile entferne, läuft das Makro sauber durch! Hurra, ohne Fehlermeldung!
Leider habe ich dann eine Datei mit korrektem Dateinamen im richtigen Ordner und wie gewünscht mit vier Arbeitsblättern, in der aber jedes Tabellenblatt komplett leer ist. Also auch das Verschieben der UsedRange funktioniert in meinem Skript nicht richtig.
Ich weiß nicht, was ich falsch mache bzw. wo meine Wissenslücke ist. Kann mir jemand bitte helfen, diese Lücke zu schließen?
Vielen Dank im Voraus.
Hier mein (aktuelles) Skript: (Die meisten Variablen habe ich Global definiert, da ich sie in verschiednen Makros brauche, daher hier kaum Dim. Nur falls es nicht eindeutig sein sollte: wksTemp as Worksheet; wkbZiel As Workbook; wkbQuelle As Workbook)
Sub Marketingsreports_erstellen()
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Application.StatusBar = "Verarbeitung läuft. Bitte warten."
Dim i As Integer
'# Vorbereitungen
Call Arbeitsmappe_vorbereiten 'Hier wird der Dateipfad ausgelesen und geprüft, ob benötigte _
Dateiordner existieren
Call Variablen_auslesen 'hier werden u.a. Stichtage aus dem Datenimport ausgelesen
' Dateinamen und Pfad festlegen
strDateiN = strPfad & "\Auswertungen Marketing\" & strJahr & "_" & strMonat & " " & " _
Marketingauswertung" & ".xlsx"
Set wkbQuelle = ThisWorkbook
'# Neues Workbook anlegen
lngAnzahlTab = Application.SheetsInNewWorkbook ' Auslesen der Standardanzahl Register Excel
Application.SheetsInNewWorkbook = 4
Set wkbZiel = Workbooks.Add ' Neue Arbeitsmappe anlegen
wkbZiel.SaveAs Filename:=strDateiN
'# Tabellen kopieren
i = 1
For Each wksTemp In wkbQuelle.Worksheets(Array("Markenlinien Marketing", _
"Cluster Marketing", "Artikel Marketing", "Trend Marketing"))
Set wksTemp = ActiveSheet
wksTemp.UsedRange.Copy Destination:=wkbZiel.Sheets(i).Range("A1")
wkbZiel.Sheets(i).Range("A1").PasteSpecial xlPasteValues
wkbZiel.Sheets(i).Range("A1").PasteSpecial xlPasteFormats
wkbZiel.Sheets(i).Name = wksTemp.Name
i = i + 1
Next wksTemp
wkbZiel.Close savechanges:=True
'# Zurücksetzen
Set wkbZiel = Nothing
Set wksTemp = Nothing
Application.CutCopyMode = False
Application.SheetsInNewWorkbook = lngAnzahlTab ' Standardanzahl Register zurücksetzen
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Application.StatusBar = False
End Sub