Results 1 to 5 of 5

Thread: Solved: Save sheet to new workbook

  1. #1

    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?

    VB:
    Sub Sheet_SaveAs() 
        Sheets("Output").Select 
        ActiveSheet.SaveAs "[URL="file://\\solon.prd\files\P\Global\Users\n00406\UserData\Documents\Project"]C:\Test[/URL] - Recon_Output_ " & Format(Date, "yyyymmdd") 
    End Sub 
    
    
    Formatting tags added by mark007
    Regards,

    RM

  2. #2
    Greetings RM,

    See if this helps:

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

  3. #3
    Hi GTO,

    It worked. Thanks a lot.

    Regards,

    RM

  4. #4
    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.
    VB:
    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 
    
    
    Formatting tags added by mark007
    With SaveAs Dialog Browsing
    VB:
    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 
    
    
    Formatting tags added by mark007

  5. #5
    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.
    VB:
    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 
    
    
    Formatting tags added by mark007
    ...
    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
  •