In this post I will share a quick tip with you. Sometimes we need to create a new workbook from existing worksheet in Excel VBA. This existing worksheet can be active worksheet or any other worksheet. Assume we have a workbook like this.
This workbook contains 3 sheets. Think we need to create a new workbook from “Sheet3”. We can do that using following code.
|
Sub CreateNewWorkbook() Dim WB As Workbook Dim WS As Worksheet Set WS = Worksheets("Sheet3") WS.Copy Set WB = ActiveWorkbook End Sub |
If you run above macro you will notice a new workbook created from Sheet3 like this.
Now we can see two workbooks in taskbar. Our original workbook and the newly created one.
And if you want to create a new workbook from active sheet you can use below code.
|
Sub CreateNewWorkbookFromActiveSheet() Dim WB As Workbook Dim WS As Worksheet Set WS = ActiveSheet WS.Copy Set WB = ActiveWorkbook End Sub |
If you want to learn how to create workbooks from each and every sheet of the workbook and save them to a desired folder, then check this post.
Create new workbooks from each and every worksheet in your workbook