Save Single Worksheet as own file

[mjmcder]

Hi All,
I have a workbook with multiple sheets in it. I am attempting to save a single worksheet from that workbook as it's own excel file. I am having issues with getting it to work, and now seem to be getting further from where I need to be. Can anyone help out. Here is what I have so far...a morph between my first attempt and my latest attempt

Dim outapp As Object
    Dim outmail As Object
    Dim FileName As String, MailSub As String, MailTxt As String
    
    Set outapp = CreateObject("Outlook.Application")
    Set outmail = outapp.CreateItem(0)
    
    'Application.ScreenUpdating = False
    
    'Variable Declaration
    PathName = Range("PortPathName").Value
    fDate = Range("MEDate").Value
    Subject = Range("PortFileName").Value
    
    'creates path & file name and gives correct extension
    FileName = PathName & ".xlsm"
    
    'creates the excel file and saves it to the Archive
    Sheets("OPEB Portfolio Recon Summary").Visible = True
    Sheets("OPEB Portfolio Recon Summary").Select
    ActiveSheet.SaveAs
    
    
    
    
    
    
    'First attempt that did not work
    ActiveWorkbook.Save
    
    ShowALL
    
    MEDate = Range("MEDate").Value
    FYDate = Range("FYDate").Value
    PortSubject = Range("PortFileName").Value
    PathName = Range("PortPathName").Value
    FileName = "OPEB Portfolio Recon " & MEDate
    
    ActiveWorkbook.SaveAs FileName:= _
        PortPathname _
        , FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
    Sheets("OPEB Portfolio Recon Summary").Select
    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Range("A1").Select
    
    
    Sheets("GASB Processor").Select
    Sheets(Array("GASB Processor", "Macro Code Information", "GASB 40 Reports", _
        "HI G40 Data", "DI G40 Data", "GASB 72 Report", "HI G72 Data", "DI G72 Data", _
        "Allocation Return Table", "Allocation History Table", "HI G74 July", _
        "HI G74 August", "HI G74 September", "HI G74 October", "HI G74 November", _
        "HI G74 December", "HI G74 January", "HI G74 February", "HI G74 March", _
        "HI G74 April", "HI G74 May", "HI G74 June", "DI G74 July", "DI G74 August", _
        "DI G74 September", "DI G74 October", "DI G74 November", "DI G74 December", _
        "DI G74 January", "DI G74 February", "DI G74 March", "DI G74 April", "DI G74 May", _
        "DI G74 June")).Select
    
    ActiveWindow.SelectedSheets.Delete
    ActiveWorkbook.Save

 

[mjmcder]

also, it should be noted that I am very green, and don't write code efficiently...more as a simpleton, replicating steps I would do manually...so try not to laugh at the above code.

 

[dreid1011]

also, it should be noted that I am very green, and don't write code efficiently...more as a simpleton, replicating steps I would do manually...so try not to laugh at the above code.

I JUST did this, here is the code I used:

strFile = "F:\Documents\VETERANS\Active Ex Exports\ActiveExemptions " & CDbl(Now()) & ".xlsx"
wsEx.Copy
ActiveWorkbook.SaveAs Filename:=strFile, FileFormat:=51

wsEx is worksheet variable set to the sheet I want to save by itself. As I learned yesterday, .Copy puts that worksheet into a new book, and sets that book as the activeworkbook. Then, ActiveWorkbook.SaveAs is saving that new book at the path designated by strFile.

 

[mjmcder]

that's awesome! Thank you so much!!!

 

[dreid1011]

You're welcome.