Sheets.Add using other worksheet as "template"

[nicholas_chuah]

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)

 

[Guest]

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

 

[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.