export sheet and name it same as doc with export on it?

[tonywatsonhelp]

Hi Everyone,

I have a tab named "Export"
I need a macro that will make a copy of the sheet as its own document then save it in the same location as current dociument, and call it "Export of " and the same name as current document.

Please help if you can

Thanks

Tony

 

[GWteB]

The code below assumes your worksheet resides in the same workbook as this code. If a workbook with an identical name already exists, it will be overwritten.

Sub tonywatsonhelp()

    Dim sFolder As String, sName As String

    With ThisWorkbook.Sheets("Export")
        sName = "Export of " & .Parent.Name
        sFolder = .Parent.Path & "\"
        .Copy
        Application.DisplayAlerts = False
        ActiveWorkbook.SaveAs Filename:=sName, FileFormat:=xlOpenXMLWorkbookMacroEnabled
        ActiveWorkbook.Close
        Application.DisplayAlerts = True
    End With
End Sub

 

[tonywatsonhelp]

Brilliant, thank yo very much