Unwanted buttons showing up in new sheet created with a Macro and Template sheet

Ok, so I have a worksheet which I have created a few macros within. This worksheet is going to function as a template for others to copy and make additional worksheets.

I figured it would us some time to create a macro that does this for us and put a button on the template sheet that copies the template into the same worksheet and makes renames it to "RENAME ME".

Worked fine, however, when I run the macro by selecting the "Create New Sheet from Template" button, I am getting unwanted buttons showing up right above the orginal buttons, both in the template, and the new sheet.

Any ideas on how I can correct this??? Thanks!

Originally Posted by MarvinP

Hi,
When you copy entire sheets and do a simple paste, shapes are copied and pasted too.
Try to change your macro to Paste Special and only paste Values or Formulas but not ALL.

That is part of my problem. I am not experienced enough in VBA to be able to modify my Macros to do what I want them to do. I actually used the macro recorder. I started recording and simply right clicked on my template worksheet (which has numerous macros that I made for the sheet) and clicked the Move or Copy selection, then selected the worksheet, checked the Create a Copy box, and made a copy of the workbook. I then renamed the new tab "RENAME ME" and changed its color.

Here is the code from my Macro if it helps:

Please Login or Register  to view this content.

Originally Posted by MarvinP

Hi -
Your code adds some buttons to the "Template - In Town" sheet then adds a new sheet and copies the first to the new one. Then it renames the new sheet to "Rename Me"

Not really sure why it would be adding additional buttons to the "Template - In Town" sheet. Maybe if I explained how I got here it would help. Basically, I used the record macro feature and simply right clicked on the "Template-In Town" sheet, selected move or copy, selected the template sheet, and checked the create a copy box. I then renamed the new sheet to "RENAME ME" and changed the tab color and pressed Stop recording. Unfortunately, my knowledge of writing macros is limited to this record macro feature (maybe someday I will learn more), so I am not sure how or why it would want to remake the buttons again.

We really need more of the problem to see how creating a new worksheet helps. Also I don't see how any of these buttons are assigned to macros. I believe the creator of the Template, expected you to run it once and save it to another workbook name and not to save the template itself.

It is just hard to tell what the code does in the big picture, much less how the Template fits in as well as the new worksheet.

I tried the code you provided but I am getting a runtime error 438.

To help with the big picture, I have attached the workbook. Basically, I am just trying to make an Job bidding sheet at my company a little more user friendly by adding some drop down lists, buttons linked to macros, etc. Previously, they would use the same template (less the validation lists, vlookups, etc that are there now) and would simply copy a new template and rename it to start a new bid. The original template is kept in the same workbook as are all of the bids for each year. I plan on keeping the template within the workbook, but just wanted to create a button that would save the time it takes to create a copy of the template.

To summarize, I would like to add a button on the very top of the "Template-In Town" worksheet, that says "Create a New Bid Using Template" or something along those lines which is attached to a Macro, which just copies the template in a new sheet, renames it to "RENAME ME" and changes the tab color from red to no color.

Thanks so much for your patience and I appreciate all the help!

Originally Posted by MarvinP

Hi fsurf02,
Now I understand the problem. You have a great idea to produce a standardized worksheet for making bids or invoices, etc. You decided you needed VBA to create this and recorded a macro from the template sheet. You're looking for the next step to clean up your VBA code so it is useful. This is a great idea and many companies have this ability. The problem is that you need to varaiableize your macro code to fit your template.

Specifically, when you add a Material and follow the Macro, it inserts a row and then shifts all rows down. When this happens it messes up the rest of the macros for adding other things.

This is the first time I have ever suggested this, but IMHO you will need to hire a VBA programmer to work through your recorded code and make your process work.

The other alternative it to learn enough VBA to do it yourself. You are looking at a few day job for a good programmer or a few weeks to develope, test, redesign, and get a final product.

Perhaps another of the Forum Gurus has a similar application that they have put together and can give you as an example.

Thanks for the recommendation. Unfortunately, it is a rather small business and we cannot afford to do such a thing. I was just hoping to brush up a few of the worksheets given knowledge I have with excel (albeit limited). I was able to figure out my inital problem with copying the new template with the button (and eliminating the additional buttons).

I do see what you were talking about with my macros to add a line. Someone in another thread showed me a pretty helpful macro to do what I was trying to do in a different manner. If you are interested, the code is as follows:

Please Login or Register  to view this content.

Thanks again for the help.