Tuesday 24 February 2015

Export Excel Worksheet(s) to a new workbook in Excel - VBA

Welcome to Logically Proven blog.

This post demonstrates how to export a particular sheet or all worksheets to a new workbook using excel VBA (Visual Basic for Applications).

In certain situations, it is required to export a current workbook data to a new workbook or do you want to save a backup of some worksheets of a workbook. In either of these cases, you can use the following VBA code to accomplish this task.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
Sub CreateCopy() 
    Dim w As Worksheet, nb As Workbook, s As String 
    For Each w In ThisWorkbook.Worksheets 
        If w.Name <> "Approvals" And w.Name <> "Calcs" Then 
            If nb Is Nothing Then 
                w.Copy 'creates new workbook and copies sheet to it
                Set nb = ActiveWorkbook 
            Else 
                w.Copy after:=nb.Sheets(nb.Sheets.Count) 
            End If 
        End If 
    Next w 
    s = ThisWorkbook.FullName 
    s = Left(s, Len(s) - 4) & "2.xls" 
    nb.SaveAs s 
End Sub 

In this example,

nb is the new workbook where we are exporting the worksheets.

In line 3: We are iterating through all the available worksheets in the currently working workbook.

In line 4: In case if you don't want to export some sheets, exclude those sheets. In this example I am excluding the sheets "Approvals" and "Calcs". If you want to export all the worksheets, remove this condition.

In line 5,6: If new workbook is not created, it creates a new workbook and copies worksheet.

In line 9: Copies the worksheet at the end (after last sheet) in the new workbook. You can modify this line if you want to change the order of the worksheets in the new workbook.

In line 13: Getting the name of the current workbook including the path. (.FullName fetches the name of the workbook as well as the path of the workbook)

In line 14: Setting the new workbook name. Consider the currently working workbook name is "ExportData.xlsm", this line sets the new workbook name as "ExportData2.xls".

In line 15: Saving the workbook.

Please write your comments if you find anything is incorrect or do you want to share more information about the topic discussed above.


Logically Proven
Learn, Teach, Share

Karthik Byggari

Author & Editor

Computer Science graduate, Techie, Founder of logicallyproven, Love to Share and Read About pprogramming related things.

0 comments:

Post a Comment

 
biz.