Macro to create new sheet

[Kim]

Hi,

I need to know how can I create a macro where it will create an exact copy.

The work book will have a few sheet and there will be one main sheet which
will be call Master. In the Master sheet, I want to create a button where
they click on it, it will create a new exact sheet. In the new sheet, it
should contain the same button where everytime I click on it, it will create
a new sheet based on the Master sheet.

Anyone can help ?

 

[Jacob Skaria]

Place a button in MasterSheet and assign the below macro. If you are new to
macros; launch VBE using Alt+f11. Insert a module and paste the below code.
Insert a shape to your master. Right click .Assign macro and select the below
Sub

Sub CreateNewSheet()
ActiveSheet.Copy
End Sub

If this post helps click Yes

 

[JLatham]

To set it up to copy the MASTER sheet rather than copies of the sheet that
have the button on it, use this code:

Sub MakeCopyOfMasterSheet()
Sheets("Master").Copy After:=Sheets(Sheets.Count)
End Sub

That will place the copy of it at the end of the workbook, and the button
will be on it.

As for the button, I kind of like using the Text Box from the Drawing
toolbar. Put one of those on the Master sheet and put appropriate text in
it, you can even set the colors in it nicely. Finally, right-click on the
edge of the shape (text box) and choose Assign Macro and choose the
MakeCopyOfMasterSheet macro from the list and you should be good to go.
Jacob Skaria gave instructions on how to get the code into the workbook.

Difference between his code and mine: My code always copies the Master
sheet, his code copies whatever sheet you happen to be on when you click the
button. One of the two of us should be right.

 

[Kim]

Hi Jacob,

Thanks. But I want to create a sheet in the same workbook or additional tab.
At the moment, the macro you give is create a new workbook.

Regards,

 

[Kim]

It work fine now. Thanks.

Now all I need is new macro to assign to move to next or previous. I've
posted another discussion group.

 

[JLatham]

Next or Previous what? Cell? Sheet? Something else?

Got an answer yet?