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
                    ' 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
                    Exit For
                End If

End Sub

1 comment:

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


Import data from SQL

Macro to import data from SQL using ADO connection string: Sub Import_data_from_SQL() ' Tools -> References -> Microsoft Active...