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.
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
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
Computer Science graduate, Techie, Founder of logicallyproven, Love to Share and Read About pprogramming related things.
0 comments:
Post a Comment