how to save worksheets in another excel file?

[maryam]

I used these VB codes to save my worksheet in another excel file. How can I save more than one sheet?
Private Sub CmdSave_Click()
Dim CurWkbook As Workbook
Dim SheetToSave As Worksheet
Dim newWkbook As Workbook
Dim sFileName As String

'Show the SaveAs dialog. sFileName is the path to save to
sFileName = Application.GetSaveAsFilename(InitialFileName:="", fileFilter:="Microsoft Office Excel Workbook (*.xls), *.xls", Title:="Save mySheet to..")

'User didn't choose to save, we exit
If sFileName = "False" Then Exit Sub

'Prevent screen from changing
Application.ScreenUpdating = False

Set CurWkbook = Application.ActiveWorkbook
Set SheetToSave = CurWkbook.Sheets(2) 'Maryam: select other sheets to save other sheets

'Create a new workbook and copy SheetToSave to new workbook
Workbooks.Add (xlWBATWorksheet)
Set newWkbook = ActiveWorkbook
newWkbook.SaveAs Filename:=sFileName
SheetToSave.Copy Before:=newWkbook.Sheets(1)

'Save and close new workbook
ActiveWorkbook.Save
ActiveWorkbook.Close

'Allow screen updating
Application.ScreenUpdating = True

'Inform user sheet has been saved
MsgBox Prompt:=SheetToSave.Name & " saved to " & sFileName, _
Buttons:=vbOKOnly + vbInformation, _
Title:="Sheet Saved"
End Sub
Private Sub CommandButton2_Click()
FrmComponent.Show
End Sub

[Bob Phillips]

If you do it in Excel with the macro recorder switched on, you will get the code to do this.

[maryam]

these codes are in VBA in Excel. What is macro recorder? How to put it on? Would you pls explain more?

[mdmackillop]

Tools/Macros/Record new macro.
Carry out the required actions then Stop Recording.
You can view/edit your recorded code in the VBE.

[Norie]

What do you actually want to copy?

The following will copy Sheet1 and Sheet2 to a new workbook and save it with the name NewWorkbook.


Sheets(Array("Sheet1", "Sheet2")).Copy

Set wb = ActiveWorkbook

wb.SaveAs ThisWorkbook.Path & "\NewWorkbook.xls"

wb.Close

[maryam]

I cannot find view/ edit. what can we save with macro recorder? how does it work?

[Bob Phillips]

View and edit are terms Malcolm was using, they are not buttons.

If you record a macro, you can then go into the VBIDE (Alt-F11), and see the code that you have just created (view), and change it (edit).

[mdmackillop]

Look for "Create a macro" in Excel help

[maryam]

Dear Xld and Malcolm now I know what is macro recorder, but I cannot find the suitable codes for doing this job. I want to save two sheets into another workbook, but I want the standard save form to open and the user define the path. Now post#1 does this for saving one sheet . I cannot change this Set SheetToSave = CurWkbook.Sheets(2) to Set SheetToSave= CurWkbook.Sheets(Array("Sheet1", "Sheet2")). It gives Debug. So how to change the codes?

[maryam]

I also changed Dim SheetToSave As Worksheets instead of Worksheet but still it gives mismatch type error.

[lucas]

maram,
I can offer just a hint here....if you wish to use this:
'Show the SaveAs dialog. sFileName is the path to save to
sFileName = Application.GetSaveAsFilename(InitialFileName:="", _
fileFilter:="Microsoft Office Excel Workbook (*.xls), *.xls", Title:="Save mySheet to..")


I want the standard save form to open and the user define the path.
then you might as well run it twice and just set your
Set SheetToSave = CurWkbook.Sheets(2)
to
Set SheetToSave = CurWkbook.ActiveSheet
and run it on each one while they are active.....

If you wish to do them at the same time you can set it up to activate each sheet you wish to run it on then use the activesheet code or you could use the array that Norie has offered but you will have to name them in the code...

I'm sure there will be better suggestions....

[maryam]

I want to save both sheets together. I tried array It means i did these changes to my codes (#1) but it gives mismatch type debug:
Dim SheetToSave As Worksheets instead of Dim SheetToSave As Worksheet
Set SheetToSave = CurWkbook.sheets(Array("Sheet1", "Sheet2"))

[lucas]

do you wish to use the saveas dialog for them to name the new workbooks?

[Norie]

Why do you have that code?

Did you try the code I posted at all?

[maryam]

Dear Norie,
i also want the standard save window to open and to the user select where to save the file.

[maryam]

Norie that will open the new workbook in the same file. then it will make my file huge and it takes time every time to open or save.

[Charlize]

What about this one ...
'File to save to
Dim sFile
'current workbook
Dim cWB As Workbook
'destination workbook
Dim oWB As Workbook
'worksheets in cWB
Dim oWS As Worksheet
'destination worksheet
Dim dWS As Worksheet
'current workbook current sheet range
Dim oWSr As Range
'destination workbook sheet to copy to range
Dim dWSr As Range
'declare cWB as the workbook to start with
Set cWB = ActiveWorkbook
'choose file to save to
sFile = Application.GetSaveAsFilename(InitialFileName:="", _
fileFilter:="Microsoft Office Excel Workbook (*.xls), *.xls", Title:="Save mySheet to..")
'sFile = Application.GetOpenFilename("Excel files (*.xls), *.xls")
If sFile <> False Then
Set oWB = Workbooks.Add
With oWB
For Each oWS In cWB.Worksheets
'create sheets of source in destination and select range
Set oWSr = cWB.Worksheets(oWS.Name).Range("A1:B1")
'add worksheet in newly created workbook and name it the same
.Worksheets.Add(after:=.Worksheets(.Worksheets.Count)).Name = oWS.Name
'create destination range to copy to
Set dWSr = .Worksheets(oWS.Name).Range("A1:B1")
'do the copy
oWSr.Copy dWSr
'next sheet in cWB if there is one
Next oWS
'save as chosen filename
.SaveAs Filename:=sFile
End With
End IfCharlize

[Norie]

Norie that will open the new workbook in the same file. then it will make my file huge and it takes time every time to open or save.
Eh, no it won't.:dunno

[ironj32]

Norie is right, it doesn't. Thanks for the code Norie...just happened to stumble across this thread the other day and it was exactly what i was looking for! Now, do you know how to delete the "newworkbook". I am using this to return a survey in email and i would like to delete the new workbook once the email is sent.

[maryam]

charlize it gives me debug that :"cannot rename the sheet to the same name as another sheet, a refrenced object library or..."
Dear Norie I want to save it in a seprate file still.

[Charlize]

charlize it gives me debug that :"cannot rename the sheet to the same name as another sheet, a refrenced object library or..."
Of course, you'll have to check if the name that you want to add, already exists. If so, you could add the data of the current workbooksheet to the copytoworkbooksheet and skip the creation of the sheet otherwise create and copy.

Charlize

[Norie]

The code I posted saves into a new workbook.