ich habe mal wieder ein Problem mit einem Makro. Ich habe mit dem Rekorder mir ein Makro aufgenommen. Die Datei enthält einige Tabellenblätter und einige dieser sollen einzeln abgespeichert werden in einem anderen Ordner. Gerne so wie sie auch in der Datei benannt sind. Das Makro funktioniert zwar so weit, aber ich muss jedes mal den Dateityp bestätigen. Bei mehreren Tabellenblättern doch etwas lästig. Gibt es da eine Möglichkeit das zu umgehen?
Falls relevant: Ich nutze Office 2016
Code:
Sub SpeicherVersuch1000()
' SpeicherVersuch1000 Makro
Sheets("Berlin").Select
Sheets("Berlin").Copy
ChDir "C:\Test"
ActiveWorkbook.SaveAs Filename:="C:\Test\Berlin.xlsx", FileFormat:= _
xlOpenXMLWorkbook, CreateBackup:=False
ActiveWindow.Close
Sheets("Burg").Select
Sheets("Burg").Copy
ActiveWorkbook.SaveAs Filename:="C:\Test\Burg.xlsx", FileFormat:= _
xlOpenXMLWorkbook, CreateBackup:=False
ActiveWindow.Close
Sheets("Cottbus").Select
Sheets("Cottbus").Copy
ActiveWorkbook.SaveAs Filename:="C:\Test\Cottbus.xlsx", FileFormat:= _
xlOpenXMLWorkbook, CreateBackup:=False
ActiveWindow.Close
Sheets("Dresden").Select
Sheets("Dresden").Copy
ActiveWorkbook.SaveAs Filename:="C:\Test\Dresden.xlsx", FileFormat:= _
xlOpenXMLWorkbook, CreateBackup:=False
ActiveWindow.Close
Sheets("Erfurt").Select
Sheets("Erfurt").Copy
ActiveWorkbook.SaveAs Filename:="C:\Test\Erfurt.xlsx", FileFormat:= _
xlOpenXMLWorkbook, CreateBackup:=False
ActiveWindow.Close
Sheets("Hamburg").Select
Sheets("Hamburg").Copy
ActiveWorkbook.SaveAs Filename:="C:\Test\Hamburg.xlsx", FileFormat:= _
xlOpenXMLWorkbook, CreateBackup:=False
ActiveWindow.Close
Sheets("Hannover").Select
Sheets("Hannover").Copy
ActiveWorkbook.SaveAs Filename:="C:\Test\Hannover.xlsx", FileFormat:= _
xlOpenXMLWorkbook, CreateBackup:=False
ActiveWindow.Close
Sheets("M?nchen").Select
Sheets("M?nchen").Copy
ActiveWorkbook.SaveAs Filename:="C:\Test\M?nchen.xlsx", FileFormat:= _
xlOpenXMLWorkbook, CreateBackup:=False
ActiveWindow.Close
Sheets("NDSMitteBremen").Select
Sheets("NDSMitteBremen").Copy
ActiveWorkbook.SaveAs Filename:="C:\Test\NDSMitteBremen.xlsx", FileFormat:= _
xlOpenXMLWorkbook, CreateBackup:=False
ActiveWindow.Close
Sheets("NDSWest").Select
Sheets("NDSWest").Copy
ActiveWorkbook.SaveAs Filename:="C:\Test\NDSWest.xlsx", FileFormat:= _
xlOpenXMLWorkbook, CreateBackup:=False
ActiveWindow.Close
Sheets("Potsdam").Select
Sheets("Potsdam").Copy
ActiveWorkbook.SaveAs Filename:="C:\Test\Potsdam.xlsx", FileFormat:= _
xlOpenXMLWorkbook, CreateBackup:=False
ActiveWindow.Close
Sheets("RheinMainFranken").Select
Sheets("RheinMainFranken").Copy
ActiveWorkbook.SaveAs Filename:="C:\Test\RheinMainFranken.xlsx", FileFormat _
:=xlOpenXMLWorkbook, CreateBackup:=False
ActiveWindow.Close
Sheets("RheinRuhr").Select
Sheets("RheinRuhr").Copy
ActiveWorkbook.SaveAs Filename:="C:\Test\RheinRuhr.xlsx", FileFormat:= _
xlOpenXMLWorkbook, CreateBackup:=False
ActiveWindow.Close
Sheets("Rostock").Select
Sheets("Rostock").Copy
ActiveWorkbook.SaveAs Filename:="C:\Test\Rostock.xlsx", FileFormat:= _
xlOpenXMLWorkbook, CreateBackup:=False
ActiveWindow.Close
Sheets("Salzwedel").Select
Sheets("Salzwedel").Copy
ActiveWorkbook.SaveAs Filename:="C:\Test\Salzwedel.xlsx", FileFormat:= _
xlOpenXMLWorkbook, CreateBackup:=False
ActiveWindow.Close
Sheets("Stendal").Select
Sheets("Stendal").Copy
ActiveWorkbook.SaveAs Filename:="C:\Test\Stendal.xlsx", FileFormat:= _
xlOpenXMLWorkbook, CreateBackup:=False
ActiveWindow.Close
Sheets("Stuttgart").Select
Sheets("Stuttgart").Copy
ActiveWorkbook.SaveAs Filename:="C:\Test\Stuttgart.xlsx", FileFormat:= _
xlOpenXMLWorkbook, CreateBackup:=False
ActiveWindow.Close
ActiveWindow.ScrollWorkbookTabs Sheets:=4
Sheets("Westfalen").Select
Sheets("Westfalen").Copy
ActiveWorkbook.SaveAs Filename:="C:\Test\Westfalen.xlsx", FileFormat:= _
xlOpenXMLWorkbook, CreateBackup:=False
ActiveWindow.Close
Sheets("WestfalenNord").Select
Sheets("WestfalenNord").Copy
ActiveWorkbook.SaveAs Filename:="C:\Test\WestfalenNord.xlsx", FileFormat:= _
xlOpenXMLWorkbook, CreateBackup:=False
ActiveWindow.Close
End Sub
Vielen Dank :)
LG
Alex