P
Pierre
Using the following code to save a single sheet and prompt for
location to save. Now, need to save ALL sheets in that new workbook.
The names of the sheets can vary from week to week.
What to change? Many thanks for thoughts.
Pierre
Sub Create_New_Workbook_Inventory()
'Save with formatting Macro
'
Sheets("Sheet1").Select
Cells.Select
ActiveSheet.Copy
Set newbk = ActiveWorkbook
newbk.ActiveSheet.Cells.Copy
newbk.ActiveSheet.Cells.PasteSpecial Paste:=xlPasteValues
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
InitialName = ("Weekly_Inventory") & ".xls"
FName = Application.GetSaveAsFilename(InitialFileName:=InitialName, _
fileFilter:="Excel 97-2003 Workbook (*.xls), *.xls")
newbk.SaveAs Filename:=FName, FileFormat:=xlExcel8
End Sub
location to save. Now, need to save ALL sheets in that new workbook.
The names of the sheets can vary from week to week.
What to change? Many thanks for thoughts.
Pierre
Sub Create_New_Workbook_Inventory()
'Save with formatting Macro
'
Sheets("Sheet1").Select
Cells.Select
ActiveSheet.Copy
Set newbk = ActiveWorkbook
newbk.ActiveSheet.Cells.Copy
newbk.ActiveSheet.Cells.PasteSpecial Paste:=xlPasteValues
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
InitialName = ("Weekly_Inventory") & ".xls"
FName = Application.GetSaveAsFilename(InitialFileName:=InitialName, _
fileFilter:="Excel 97-2003 Workbook (*.xls), *.xls")
newbk.SaveAs Filename:=FName, FileFormat:=xlExcel8
End Sub