Save all sheets in a new workbook

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
 
D

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?
 
D

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 ...............
 
D

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
 
P

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
 
D

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
 
P

Pierre

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

Many thanks.

Pierre
 
D

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top