Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to create new sheet
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 ? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to create new sheet
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 --------------- Jacob Skaria "Kim" wrote: 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 ? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to create new sheet
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" wrote: 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 ? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to create new sheet
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, "Jacob Skaria" wrote: 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 --------------- Jacob Skaria "Kim" wrote: 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 ? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to create new sheet
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" wrote: 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" wrote: 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 ? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to create new sheet
Next or Previous what? Cell? Sheet? Something else?
Got an answer yet? "Kim" wrote: 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" wrote: 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" wrote: 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 ? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
macro to create, name sheet and copy. | Excel Discussion (Misc queries) | |||
How to create macro to go to certain date within excel sheet? | Excel Discussion (Misc queries) | |||
How can you create a macro on a protected sheet? | Excel Discussion (Misc queries) | |||
create a macro to save excel sheet | Excel Worksheet Functions | |||
How do I create a macro button and place anywhere on my sheet? | Excel Discussion (Misc queries) |