Save Worksheet as Workbook (Excel 2003)

Topic

New Reply

I have a client who wishes to save a worksheet as a workbook. Initially, they asked that each worksheet be saved as a seperate workbook, I found this code and it works great; however; now the story has changed… they want a specific worksheet to be saved as a workbook and for the user to be able to select the location where the sheet was being saved at the point of the VBA being run. Unfortunately, I write VBA in Word and have not worked with Excel VBA at all… any ideas on how we can modify this code to do what they want? Thanx in advance… trish

Sub ExportSheets()
For Each mySht In ActiveWorkbook.Worksheets
mySht.Copy
ActiveWorkbook.SaveAs “C:Excel” & ActiveSheet.Name
ActiveWorkbook.Close
Next mySht
End Sub

Reply | Quote

Replies

You could use

Application.Dialogs(xlDialogSaveAs).Show

to let the user select a path and file name.

Reply | Quote

For anyone else interested… found this code which copies the current sheet and pastes it into a new workbook that the user then saves where/whenever…

Sub MakeNewBook()
Dim FreshWorkBook As New Workbook

Application.ScreenUpdating = False

Set FreshWorkBook = Workbooks.Add
With FreshWorkBook
ThisWorkbook.ActiveSheet.Copy before:=.Sheets(1)
End With

End Sub

Reply | Quote

You could do something like this:

Sub ExportSheets()
    Worksheets("nameofsheettocopy").Copy
    Application.Dialogs(xlDialogSaveAs).Show
    ActiveWorkbook.Close
End Sub

Reply | Quote