I used these VB codes to save my worksheet in another excel file. How can I save more than one sheet?
[VBA]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[/VBA]