Wednesday, August 9, 2017

Excel VBA- Worksheet as a Workbook

How to save a worksheet as a workbook in Excel VBA

Sub WorksheetAsWorkbook(WS As Worksheet)
    Dim WB As Workbook
    Dim strPath


    WS.Range("A1:AZ5000").Copy
    Set WB = Workbooks.Add
    ActiveSheet.Paste
    Application.CutCopyMode = False
    strPath = ThisWorkbook.Path & "\SavedSheetAsWorkbookFiles\" & WS.Name & "_" & Format(Date, "YYYYMMDD") & Hour(Now()) & Minute(Now()) & Second(Now) & ".xlsx"
    WB.SaveAs Filename:=strPath, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
    WB.Close True



End Sub

No comments:

Post a Comment

Hot Topics