Macro To Create New Workbook and Sheets

[MikeG]

I wonder if someone can help. In the macro below, I start in my main workbook and I make a copy of one of the sheets, called "Customer Report" to a new workbook. I then save this new workbook under the name "New Workbook", close it and therefore return to the main workbook.

I would like to add another task to this macro.

In the main workbook, I also have a worksheet called "Products".

What I would like to do is, once the new workbook has been created, I would like to then add a copy of the "Products" worksheet to the new workbook. And in addition, I would like to rename this worksheet as "Current Products". So the new workbook will have two sheets - "Customer Report" and "Current Products".

I have put where I think the new code will go (that's the easy part!).

Thanks,

MikeG

=================================================

Sub Create_New_Workbook()

Dim wbNew As Workbook, wbCur As Workbook


Set wbCur = ActiveWorkbook

ThisWorkbook.Worksheets("Customer Report").Copy

Set wbNew = ActiveWorkbook


'New code goes here I think

With wbNew
.SaveAs Filename:=Range("New Workbook") & ".xlsx"
End With

wbNew.Close

End Sub

 

[VoG]

Maybe (untested)

Sub Create_New_Workbook()
Dim wbNew As Workbook, wbCur As Workbook
Set wbCur = ActiveWorkbook
ThisWorkbook.Worksheets(Array("Customer Report", "Products")).Copy
Set wbNew = ActiveWorkbook
With wbNew
    .Sheets("Products").Name = "New Products"
    .SaveAs Filename:=Range("New Workbook") & ".xlsx"
End With
wbNew.Close
End Sub

 

[MikeG]

Maybe (untested)

Sub Create_New_Workbook()
Dim wbNew As Workbook, wbCur As Workbook
Set wbCur = ActiveWorkbook
ThisWorkbook.Worksheets(Array("Customer Report", "Products")).Copy
Set wbNew = ActiveWorkbook
With wbNew
    .Sheets("Products").Name = "New Products"
    .SaveAs Filename:=Range("New Workbook") & ".xlsx"
End With
wbNew.Close
End Sub

Thanks VoG

MikeG

 

[MikeG]

I have a related follow up question:

In the macro code below, when I get to the last line i thought it would put me into the original workbook - but it doesn't. Do I need some type of "Select" statement - e.g. curWB.Select?

Thanks

MikeG
==========================================

Sub Create_Customer_Report()

Dim wbNew As Workbook, wbCur As Workbook

Sheets("Customer Report").Select


Set wbCur = ActiveWorkbook
ThisWorkbook.Worksheets("Customer Report").Copy

Set wbNew = ActiveWorkbook

With wbNew
.SaveAs Filename:=Range("CR_File_Name") & ".xlsx"
End With

Set wbCur = ActiveWorkbook

 

[VoG]

Instead of

Set wbCur = ActiveWorkbook

wbCur.Select

 

[MikeG]

Instead of

Set wbCur = ActiveWorkbook

wbCur.Select

Thanks but I get a 438 Error "Object doesn't support this property or method"

 

[VoG]

Hmm. I'm not sure why that errors but this works

wbCur.Activate

 

[MikeG]

Hmm. I'm not sure why that errors but this works

wbCur.Activate

That works! Strange.

Thanks,

MikeG