Excel Copy active sheet and specified sheets to new workbook

Question

im trying to copy the active sheet and 2 specified sheets to a new workbook and then have the macro to continue to run on the new workbook to change a few things before i save it

i want to copy the sheets by the sheets codenames

1st Sheet 3 to copy this will be the "active sheet"
2nd Sheets codename is "DropDown_Sheet_1_VB"
3rd Sheets codename is "Control_Sheet_VB"

Example Code.

Sub Create_Work_Order()

Workbooks.Add

ActiveSheet.Copy 'copy to new workbook
DropDown_Sheet_1_VB.Copy 'also copy to new workbook
Control_Sheet_VB.Copy 'also copy to new workbook

Dim NewWB As Workbook

NewWB.ActiveSheet.Range("A1").Copy 'do stuff on new workbook
NewWB.Save

End Sub

smozgur smozgur · Accepted Answer · 2017-01-04T22:56:29

I don't know what is the stuff at the end other than copying A1 range but I put it there too.

You simply need to assign the workbook object (wrk in my sample, NewWB in your sample - variable name doesn't matter) at the beginning and tell the Copy method where to copy. You will see that I specified first parameter - which is :Before - as wrk.Worksheets(1), so copied sheets are copied just before the first worksheet in the new workbook.

Sub Create_Work_Order()
Dim currentWrk As Workbook
Dim wrk As Workbook

Set currentWrk = ActiveWorkbook
Set wrk = Workbooks.Add

currentWrk.ActiveSheet.Copy wrk.Worksheets(1)
DropDown_Sheet_1_VB.Copy wrk.Worksheets(1)
Control_Sheet_VB.Copy wrk.Worksheets(1)

'wrk.ActiveSheet.Range("A1").Copy 'do stuff on new workbook
wrk.Save

' EDIT: I added following to change the functions to refer to the new file
wrk.ChangeLink currentWrk.FullName, wrk.FullName, xlExcelLinks  
wrk.Save


End Sub

Excel Copy active sheet and specified sheets to new workbook

1 Answers