Inserting a new worksheet into a workbook...

Posted by Paul on September 12, 2001 7:22 AM

Hi,

Quite simply i want to insert a new worksheet into my existing workbook using a macro button placed on the menu bar.

I have a worksheet that contains formulae that refer to other worksheets by name. These sheets are part of a workbook that will be openned and i would like to be able to press my shortcut button on the menu bar and insert my worksheet into the workbook so that my refereces acually mean something.

I hope this makes sense. Can anybody help me with the code that will allow me to do this?

I don't know the name of the workbook that i will be inserting the template worksheet into if this is part of the puzzle.

Thanks in advance

Posted by Juan Pablo on September 12, 2001 7:58 AM

I think that simply putting this code will work, because excel is "intelligent" enough, to put it a new name, unless you want a specific name...

The code is

Worksheets.add

That's it...

Hope that helps

Juan Pablo

Posted by Paul on September 12, 2001 8:06 AM

Thanks, but...

Thanks for your help,

This adds a new worksheet but i want to add a specific worksheet i.e. my worksheet containing all my formulas.

Will i have to add a formula to each cell using code...?

Thanks again

Posted by Juan Pablo on September 12, 2001 10:43 AM

Re: Thanks, but...

Oh, that's another thing, then try,

Sheets("Your source sheet").Copy

If you want to handle it more (Like changing the name and other things) then try this one...

Sub InsertSheet()
Dim NewSheet As Worksheet

Sheets("Source Sheet").Copy After:=Worksheets(Worksheets.Count)
Set NewSheet = Worksheets(Worksheets.Count)
With NewSheet
.Name = "New Sheet"
'Other things
End With
End Sub

That way you get a copy of the source sheet, including the formulas...

That helps ?

Juan Pablo

Posted by Richard S on September 12, 2001 11:45 PM

Re: Thanks, but...

I'm nota VBer, but wouldn't this mean Paul would have to have a workbook open with the relevant worksheet? What if he created a worksheet template that had all his formulae it it, and your code inserted that template?

Richard

Posted by Juan Pablo on September 13, 2001 7:21 AM

Re: Thanks, but...

Then, if the code is implemented correctly, the template would be copied... i'm doing this right now, and what i do is a keep the "source sheet" very hidden, and then copy it so the user can do his stuff in it, without damaging the original.

And yes, it assumes that the workbook has to be open.