Setup Tab as Template and copy as needed

[bradjedis]

Greetings all,

Have an issue where I need to set up sheet as a "template" then have a button on that sheet which will copy the contents of the template to a new tab.

The button should only be available on the Template sheet.

Any ideas?

Thanks,
Brad

[HansV]

Use a command button from the Forms toolbar, so that you can assign a macro in a standard toolbar to it.

Here is the macro:

Code: Select all

Sub CopySheet()
  Dim wshOld As Worksheet
  Dim wshNew As Worksheet
  Set wshOld = ActiveSheet
  wshOld.Copy After:=Worksheets(Worksheets.Count)
  Set wshNew = Worksheets(Worksheets.Count)
  wshNew.Buttons(1).Delete
End Sub

This assumes that the command button will be the first (or only) one on the sheet.

[bradjedis]

Thanks Hans.

One question, I do not seem to ba able to locate the forms toolbar. I am in Excel 2007.


Thanks,
Brad

[HansV]

Activate the Developer tab of the ribbon.
(If you do not see it, click the Office button, then Word Options. If necessary, click Popular. Tick the check box "Show Developer tab in the Ribbon". Click OK.)
Click the Insert button in the Controls group of the ribbon.
The top half of the palette that drops down contains the controls from the Forms toolbar.

[bradjedis]

Wow, I got side tracked. Thanks for the detail!

Brad