Jedes Arbeitsblatt einzeln speichern, nicht das 1.
Ruth
leider habe ich im Archiv nichts gefunden:
ich möchte jedes Arbeitsblatt einzeln speichern, bis auf das 1. Blatt.
Zur Zeit nutze ich ein aufgezeichnetes Makro, in dem ich jedes einzelne Blatt selektiert habe. Das Problem habe ich, wenn die Namen der Arbeitsblätter sich verändern oder eines hinzukommt. Dann muss ich immer an das Makro ran.
Ich weiß aber, daß Ihr solche Tricks drauf habt, die Makros flexibel zu gestalten :-)
Könnt Ihr mir Tipps geben?
Hier ist mein Makro: nicht erschrecken, ist aufgezeichnet ;-)
Private Sub CommandButton1_Click()
' Sheet LES:'
Sheets("LES").Select
Sheets("LES").Copy
ActiveSheet.Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
ChDir "P:\CONTR\Report2011\Monthly_MEU_Rep\Sent_Files"
ActiveWorkbook.SaveAs Filename:= _
"P:\CONTR\Report2011\Monthly_MEU_Rep\Sent_Files\" & " Monthly report MEUGER_" & Range(" _
_
_
_
_
_
A3").Value & "_" & ActiveSheet.Name & ".xls" _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
ActiveWindow.Close
'Sheet VIS:'
Sheets("VIS").Select
Sheets("VIS").Copy
ActiveSheet.Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
ActiveWorkbook.SaveAs Filename:= _
"P:\CONTR\Report2011\Monthly_MEU_Rep\Sent_Files\" & " Monthly report MEUGER_" & Range(" _
_
_
_
_
_
A3").Value & "_" & ActiveSheet.Name & ".xls" _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
ActiveWindow.Close
Sheets("Sheet1").Select
' Sheet AUTO:'
Sheets("AUTO").Select
Sheets("AUTO").Copy
ActiveSheet.Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
ChDir "P:\CONTR\Report2011\Monthly_MEU_Rep\Sent_Files"
ActiveWorkbook.SaveAs Filename:= _
"P:\CONTR\Report2011\Monthly_MEU_Rep\Sent_Files\" & " Monthly report MEUGER_" & Range(" _
_
_
_
_
_
A3").Value & "_" & ActiveSheet.Name & ".xls" _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
ActiveWindow.Close
'Sheet SEMI:'
Sheets("SEMI").Select
Sheets("SEMI").Copy
ActiveSheet.Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
ActiveWorkbook.SaveAs Filename:= _
"P:\CONTR\Report2011\Monthly_MEU_Rep\Sent_Files\" & " Monthly report MEUGER_" & Range(" _
_
_
_
_
_
A3").Value & "_" & ActiveSheet.Name & ".xls" _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
ActiveWindow.Close
Sheets("Sheet1").Select
' Sheet PV:'
Sheets("PV").Select
Sheets("PV").Copy
ActiveSheet.Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
ChDir "P:\CONTR\Report2011\Monthly_MEU_Rep\Sent_Files"
ActiveWorkbook.SaveAs Filename:= _
"P:\CONTR\Report2011\Monthly_MEU_Rep\Sent_Files\" & " Monthly report MEUGER_" & Range(" _
_
_
_
_
_
A3").Value & "_" & ActiveSheet.Name & ".xls" _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
ActiveWindow.Close
'Sheet EBG:'
Sheets("EBG").Select
Sheets("EBG").Copy
ActiveSheet.Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
ActiveWorkbook.SaveAs Filename:= _
"P:\CONTR\Report2011\Monthly_MEU_Rep\Sent_Files\" & " Monthly report MEUGER_" & Range(" _
_
_
_
_
_
A3").Value & "_" & ActiveSheet.Name & ".xls" _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
ActiveWindow.Close
Sheets("Sheet1").Select
'Sheet IAS:'
Sheets("IAS").Select
Sheets("IAS").Copy
ActiveSheet.Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
ActiveWorkbook.SaveAs Filename:= _
"P:\CONTR\Report2011\Monthly_MEU_Rep\Sent_Files\" & " Monthly report MEUGER_" & Range(" _
_
_
_
_
_
A3").Value & "_" & ActiveSheet.Name & ".xls" _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
ActiveWindow.Close
Sheets("Sheet1").Select
' Sheet CNC:'
Sheets("CNC").Select
Sheets("CNC").Copy
ActiveSheet.Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
ChDir "P:\CONTR\Report2011\Monthly_MEU_Rep\Sent_Files"
ActiveWorkbook.SaveAs Filename:= _
"P:\CONTR\Report2011\Monthly_MEU_Rep\Sent_Files\" & " Monthly report MEUGER_" & Range(" _
_
_
_
_
_
A3").Value & "_" & ActiveSheet.Name & ".xls" _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
ActiveWindow.Close
'Sheet EDM:'
Sheets("EDM").Select
Sheets("EDM").Copy
ActiveSheet.Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
ActiveWorkbook.SaveAs Filename:= _
"P:\CONTR\Report2011\Monthly_MEU_Rep\Sent_Files\" & " Monthly report MEUGER_" & Range(" _
_
_
_
_
_
A3").Value & "_" & ActiveSheet.Name & ".xls" _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
ActiveWindow.Close
Sheets("Sheet1").Select
'Sheet EMC:'
Sheets("EMC").Select
Sheets("EMC").Copy
ActiveSheet.Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
ActiveWorkbook.SaveAs Filename:= _
"P:\CONTR\Report2011\Monthly_MEU_Rep\Sent_Files\" & " Monthly report MEUGER_" & Range(" _
_
_
_
_
_
A3").Value & "_" & ActiveSheet.Name & ".xls" _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
ActiveWindow.Close
Sheets("Sheet1").Select
'Sheet LPM:'
Sheets("LPM").Select
Sheets("LPM").Copy
ActiveSheet.Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
ActiveWorkbook.SaveAs Filename:= _
"P:\CONTR\Report2011\Monthly_MEU_Rep\Sent_Files\" & " Monthly report MEUGER_" & Range(" _
_
_
_
_
_
A3").Value & "_" & ActiveSheet.Name & ".xls" _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
ActiveWindow.Close
Sheets("Sheet1").Select
MsgBox "BU Files have been saved in P:\CONTR\...\Monthly_MEU_Rep\Sent_Files\"
End Sub
Vielen Dank im Voraus
Gruß
Ruth