PDA

View Full Version : Saving selected worksheets in VBA



andytpl
08-12-2007, 12:00 AM
Recently I asked for help with a vba codes to save the active worksheet as a workbook and below was the result of it.
Now the situation have changed a bit and now instead of saving the activeworksheet, I would now like to be able to save the selected tabs into a new workbook with user input of filename to be saved. One aspect that need attention is this workbook contains hidden worksheets that are also to be saved.

Sub exportwksht()
Dim myPath As String, NewName As String, ws As Worksheet
myPath = ThisWorkbook.Path
NewName = ActiveSheet.Name
Application.DisplayAlerts = False
For Each ws In Sheets
On Error Resume Next
If ws.Name <> NewName Then ws.Delete
Next ws
On Error GoTo 0
Application.DisplayAlerts = True
ThisWorkbook.SaveAs NewName & ".xls"
End Sub

Bob Phillips
08-12-2007, 01:58 AM
Dim mpNewname As String

mpNewname = InputBox("Supply name for new workbook")
If mpNewname <> "" Then
ActiveWorkbook.SaveCopyAs mpNewname
End If

Norie
08-12-2007, 06:33 AM
How can a hidden worksheet be selected?:eek: