Copying sheets to new workbook problem with workbooks.add VBA

[RockandGrohl]

Hi all, quick one I hope.

I'm copying 4 tabs from one workbook into a new workbook, only copying the values, formatting and column widths along with the tab name and colour.

I've done this by recording a macro, but have ran into a problem.

When the Macro gets to Workbook.Add, it adds a new workbook, Book1, right?

The problem is if I run this macro again, it'll now be Book2...


Is there a way to open a new workbook and set it as "Output" or something?

Thanks.

 

[Fluff]

In order to give the workbook a name you need to use

ActiveWorkbook.SaveAs

 

[Norie]

Are you only copying these 4 tabs to a new workbook?

If you are you could simply copy them in one go, without a destination, and then you'll have a new workbook with only the tabs you've copied.

 

[RockandGrohl]

Are you only copying these 4 tabs to a new workbook?

If you are you could simply copy them in one go, without a destination, and then you'll have a new workbook with only the tabs you've copied.

I want this to be in a macro button you press which generates a new worksheet to send to someone. The huge formulas in them must be pasted as values.