create new workbook

[sal21]

I have just have active workbook.
I need to create a new workbook via vba code and add sheet1 and sheet2 from the active workbook and save the new workbook in c:\my_dir\mynewvb.xls....

[HansV]

Like this:

Code: Select all

Sub CreateWorkbook()
  ' Copy sheets to new workbook
  ActiveWorkbook.Worksheets(Array("Sheet1", "Sheet2")).Copy
  ' Close and save the new workbook
  ActiveWorkbook.Close SaveChanges:=True, Filename:="c:\my_dir\mynewvb.xls"
End Sub

[sal21]

tKS but i dont want to close the active workbook where is the code...

[HansV]

The line

ActiveWorkbook.Worksheets(Array("Sheet1", "Sheet2")).Copy

creates a new workbook, and this new workbook becomes the active workbook. So the line

ActiveWorkbook.Close SaveChanges:=True, Filename:="c:\my_dir\mynewvb.xls"

acts on the new workbook, not on the workbook with the code. When the new workbook is closed, the workbook with the code becomes the active workbook again.

[Don Wells]

Like this:

Code: Select all

  ' Copy sheets to new workbook
  ActiveWorkbook.Worksheets(Array("Sheet1", "Sheet2")).Copy

The above line of code certainly works as advertised by Hans, but I need some help understanding it. I would have expected the two worksheets to be copied to the clipboard. Also I see nothing that obviously creates a new workbook. Is there something happening here by default?

TIA

[HansV]

From the help for the Copy method of the Worksheet(s) object:

Remarks
If you don't specify either Before or After, Microsoft Excel creates a new workbook that contains the copied sheet.

[Don Wells]

From the help for the Copy method of the Worksheet(s) object:

Thank you Hans
If all else fails, read the manual. I am embarrassed.