Save all sheets in a new workbook

[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

 

[Dave Peterson]

Can't you just do a File|SaveAs and save as a new name?

Ps.

You're also converting all the formulas to values in the original workbook, too.
Did you need that in the new workbook?

 

[Don Guillett Excel MVP]

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

Explain ...............

 

[Don Guillett Excel MVP]

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

 

[Pierre]

Dave and Don, thanks for the interest.

Need to create the workbook as new workbook containing all the pages
that currently exist in the source workbook file.
Copy all available worksheets, save as a new file in the format and
prompt as the code calls out as indicated;.paste as values, and retain
formatting.
It works fine for just saving the active sheet as a new file, just
need to save all sheets in the new workbook.

Thanks for taking a look.
Pierre

 

[Dave Peterson]

Untested:

Option Explicit
Sub Create_New_Workbook_Inventory2()

Dim wks As Worksheet
Dim wkbk As Workbook
Dim FName As Variant
Dim InitFileName As String

InitFileName = "Weekly_Inventory_" & Format(Now, "yyyymmdd_hhmmss") & ".xls"

FName = Application.GetSaveAsFilename(InitialFileName:=InitFileName, _
fileFilter:="Excel 97-2003 Workbook, *.xls")

If FName = False Then
'user hit cancel, do nothing
Exit Sub
End If

Set wkbk = ActiveWorkbook

For Each wks In wkbk.Worksheets
With wks
.Cells.Copy
.Cells.PasteSpecial Paste:=xlPasteValues
End With
Next wks

On Error Resume Next
wkbk.SaveAs Filename:=FName, FileFormat:=xlExcel8
If Err.Number <> 0 Then
MsgBox "There was an error:" _
& vbLf & Err.Number & vbLf & Err.Description
Err.Clear
Else
MsgBox "This is the new file!!!"
End If
On Error GoTo 0


End Sub

Dave and Don, thanks for the interest.

Need to create the workbook as new workbook containing all the pages
that currently exist in the source workbook file.
Copy all available worksheets, save as a new file in the format and
prompt as the code calls out as indicated;.paste as values, and retain
formatting.
It works fine for just saving the active sheet as a new file, just
need to save all sheets in the new workbook.

Thanks for taking a look.
Pierre

 

[Pierre]

Dave: It tested just fine on this end, (Took out the Option
Explicit, it clogged it up.)

Many thanks.

Pierre

 

[Dave Peterson]

"Option explict" is an instruction to the compiler that tells it that you (the
programmer) will be declaring each variable that you use.

Since the code compiled ok for me, that means you made a change and didn't
declare your variable.

It's always a good thing to declare variables and use "option explicit" in your
code.

Dave: It tested just fine on this end, (Took out the Option
Explicit, it clogged it up.)

Many thanks.

Pierre