Tuesday, February 9, 2016

Excel VBA- Split Workbook as Worksheets


EXCEL VBA: SPLIT A WORKBOOK & SAVE EACH WORKSHEET AS WORKBOOK

Sub SheetsAsWbook()
    Dim WS As Worksheet
    
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    On Error GoTo lblERR
    
    For Each WS In ThisWorkbook.Sheets
        WS.Copy
        Application.ActiveWorkbook.SaveAs _
            Filename:=ThisWorkbook.Path & Application.PathSeparator & WS.Name & ".xls"
        Application.ActiveWorkbook.Close False
    Next
    
    MsgBox "DONE", vbInformation
    
lblERR:
If Err.Number <> 0 Then
    MsgBox "Error Number:" & Err.Number & vbCrLf & "Error Description: " & Err.Number
End If
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

No comments:

Post a Comment

Hot Topics