Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Apr 2003
    Location
    Calgary, Alberta
    Posts
    208
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Save Worksheet as Workbook (Excel 2003)

    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Save Worksheet as Workbook (Excel 2003)

    You could use

    Application.Dialogs(xlDialogSaveAs).Show

    to let the user select a path and file name.

  3. #3
    3 Star Lounger
    Join Date
    Apr 2003
    Location
    Calgary, Alberta
    Posts
    208
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Save Worksheet as Workbook (Excel 2003)

    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

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Save Worksheet as Workbook (Excel 2003)

    You could do something like this:

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

    Legare Coleman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •