Sheets.Add using other worksheet as "template"
 

Hi,

I have a worksheet call MASTER

In one of the macro which create new worksheets dynamically, I need to
create the new worksheet using the worksheet MASTER.

Part of the macro:

dim ws_new as worksheet
Set ws_new = Sheets.Add

Sheets.Add just add empty worksheet, what is the right syntax to the
above macro to use the MASTER worksheet?

Nic

Sheets.Add using other worksheet as "template"
 

Unlike workbooks, there is only one worksheet template, called Sheet.xlt,
and stored in your XLStart directory. All new sheets will assume this
template, although you can save a new template file if you wish.

If you want multiples, you could store them in your Personal.xls file, and
copy rather than just add.

--

HTH

RP
(remove nothere from the email address if mailing direct)


wrote in message
ups.com...
Hi,

I have a worksheet call MASTER

In one of the macro which create new worksheets dynamically, I need to
create the new worksheet using the worksheet MASTER.

Part of the macro:

dim ws_new as worksheet
Set ws_new = Sheets.Add

Sheets.Add just add empty worksheet, what is the right syntax to the
above macro to use the MASTER worksheet?

Nic

Sheets.Add using other worksheet as "template"
 

So do you want to make a copy of a sheet called master which already exists
in your workbook? If so then...

Public Sub CopyMaster()
Dim wksMaster As Worksheet

Set wksMaster = Sheets("Master")
wksMaster.Copy Sheets(1)
Set wksMaster = Nothing

End Sub


" wrote:

Hi,

I have a worksheet call MASTER

In one of the macro which create new worksheets dynamically, I need to
create the new worksheet using the worksheet MASTER.

Part of the macro:

dim ws_new as worksheet
Set ws_new = Sheets.Add

Sheets.Add just add empty worksheet, what is the right syntax to the
above macro to use the MASTER worksheet?

Nic

Sheets.Add using other worksheet as "template"
 

Don't use "As Worksheet".

Dim ws_new As Object
Set ws_new = Sheets.Add(Type:="C:\my documents\excel\book1.xls")

This adds as many sheets as there are in the master workbook.



wrote:

Hi,

I have a worksheet call MASTER

In one of the macro which create new worksheets dynamically, I need to
create the new worksheet using the worksheet MASTER.

Part of the macro:

dim ws_new as worksheet
Set ws_new = Sheets.Add

Sheets.Add just add empty worksheet, what is the right syntax to the
above macro to use the MASTER worksheet?

Nic

--

Dave Peterson