Pages

Saturday, April 19, 2014

Save Specific Worksheet as new workbook

Macro to save specific worksheet as a new workbook-


Sub save_specific_worksheets_as_new_workbook()
    Dim wk As Worksheet
    Dim shtnames()
    shtnames = Array("Sheet1", "Sheet2", "PPT") ' ADD SHEET NAMES
    
        ' loop through each worksheet
        For Each wk In ThisWorkbook.Worksheets
            For i = LBound(shtnames) To UBound(shtnames)
                If wk.Name = shtnames(i) Then
                    ' save it in new workbook
                    wk.Copy
                    ' save the new workbook with sheet name
                    ActiveWorkbook.SaveAs ThisWorkbook.Path & "\" & wk.Name & VBA.Format(VBA.Now, "_ddmmyyyy_hhmmss") & ".xlsx"
                    ' close the newly created workbook
                    ActiveWorkbook.Close
                    Exit For
                End If
            Next
        Next

End Sub

1 comment:

  1. If you want to know more about "Add and save new workbook using vba", check this link ........

    http://www.exceltip.com/files-workbook-and-worksheets-in-vba/add-and-save-new-workbook-using-vba-in-microsoft-excel.html

    ReplyDelete