Consulting

Results 1 to 5 of 5

Thread: Solved: Save sheet to new workbook

  1. #1
    VBAX Regular
    Joined
    Jun 2008
    Posts
    52
    Location

    Solved: Save sheet to new workbook

    Hi,

    I've a workbook with a few sheets. I want to save one particular sheet to a new workbook with a predefined workbookname that also ends with a date. The code below works fine partly, because instead of saving the particular sheet, it save the whole workbook again with the pre-defined workbookname. What is wrong in my code?

    [vba]Sub Sheet_SaveAs()
    Sheets("Output").Select
    ActiveSheet.SaveAs "C:\Test - Recon_Output_ " & Format(Date, "yyyymmdd")
    End Sub[/vba]

    Regards,

    RM

  2. #2
    Knowledge Base Approver VBAX Guru GTO's Avatar
    Joined
    Sep 2008
    Posts
    3,368
    Location
    Greetings RM,

    See if this helps:

    Sub Sheet_SaveAs()
    Dim wb As Workbook
        
        '// Set as reference to a new, one-sheet workbook.                              //
        Set wb = Workbooks.Add(xlWBATWorksheet)
        With wb
            '// Skip selecting the sheet, just reference it explicitly and copy it after//
            '// the blank sheet in the new wb.                                          //
            ThisWorkbook.Worksheets("Output").Copy After:=.Worksheets(.Worksheets.Count)
            '// Kill alerts, delete the blank sheet in the new wb and turn alerts back on//
            Application.DisplayAlerts = False
            .Worksheets(1).Delete
            Application.DisplayAlerts = True
            '// SaveAs the new workbook to whatever path and close.                     //
            .SaveAs ThisWorkbook.Path & "\ - Recon_Output_ " & Format(Date, "yyyymmdd")
            .Close False
        End With
    End Sub
    Mark

  3. #3
    VBAX Regular
    Joined
    Jun 2008
    Posts
    52
    Location
    Hi GTO,

    It worked. Thanks a lot.

    Regards,

    RM

  4. #4
    VBAX Expert
    Joined
    Sep 2010
    Posts
    604
    Location
    Hi GTO,

    The two versions of code below have been working for me. No new sheet and delete required.

    I highly respect your abilities (as far above my own), and am only posting these for the purpose of sharing and learning. I am happy to have any parts of it that may be faulty pointed out to me.
    [vba]Sub Sheet_SaveAs()

    Dim wb As Workbook

    Sheets("Output").Copy

    Set wb = ActiveWorkbook

    With wb
    .SaveAs ThisWorkbook.Path & "\ - Recon_Output_ " & Format(Date, "yyyymmdd")
    .Close False
    End With

    End Sub[/vba] With SaveAs Dialog Browsing
    [vba]Sub Sheet_SaveAs_Dialog()

    Dim wb As Workbook, InitFileName As String, fileSaveName As String

    InitFileName = ThisWorkbook.Path & "\ - Recon_Output_ " & Format(Date, "yyyymmdd")

    Sheets("Output").Copy
    ' or below for more than one sheet
    ' Sheets(Array("Output", "Sheet2", "Sheet3")).Copy

    Set wb = ActiveWorkbook

    fileSaveName = Application.GetSaveAsFilename(InitialFileName:=InitFileName, _
    filefilter:="Excel files , *.xlsx")

    With wb
    If fileSaveName <> "False" Then

    .SaveAs fileSaveName
    .Close
    Else
    .Close False
    Exit Sub
    End If
    End With

    End Sub[/vba]

  5. #5
    Knowledge Base Approver VBAX Guru GTO's Avatar
    Joined
    Sep 2008
    Posts
    3,368
    Location
    Quote Originally Posted by frank_m
    Hi GTO,

    The two versions of code below have been working for me. No new sheet and delete required.

    I highly respect your abilities (as far above my own), and am only posting these for the purpose of sharing and learning. I am happy to have any parts of it that may be faulty pointed out to me.
    [vba]Sub Sheet_SaveAs()

    Dim wb As Workbook

    Sheets("Output").Copy

    Set wb = ActiveWorkbook

    With wb
    .SaveAs ThisWorkbook.Path & "\ - Recon_Output_ " & Format(Date, "yyyymmdd")
    .Close False
    End With

    End Sub[/vba]
    ...
    Greetings Frank,

    Thank you for the compliment; as a 'laymen' (at best somedays) coder, I learn something new each day, or at least every chance I get to spend some time here.

    For what it's worth, IMO, there is certainly nothing wrong and no real disadvantage to the method you used. I have used that way myself, but am simply in the habit of setting a reference to the object wb at creation, rather than grabbing the active wb. Again, really just a preference on my part.

    Mark

Posting Permissions

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