Sheets.Add using other worksheet as "template"

[nicholas_chuah@yahoo.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

[Bob Phillips]

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)


<nicholas_chuah@yahoo.com> wrote in message
news:1114453232.003271.63130@z14g2000cwz.googlegroups.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
>

[Jim Thomlinson]

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


"nicholas_chuah@yahoo.com" 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]

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.



nicholas_chuah@yahoo.com 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