Adding macro buttons to new worksheets

G

Guest

I have a workbook with 3 sheets, Budget, Extract and Summary where Summary
contains a Pivot Table which is macro generated from data in the Budget and
Extract sheets.

I've got macro buttons (auto-shapes) on the 3 sheets and I want to add macro
buttons to the new sheet which is generated when a cell is double-clicked in
a pivot table (to show the detail behind the summary).

These sheets will always be called Sheet1, Sheet2 etc.

Is it possible to run a macro automatically each time a new worksheet which
begins with "Sheet" is generated? If so, how do I do this please as I'm stuck?

Thanks a lot in anticipation.
 
C

Charles Chickering

In the VBE, find the "ThisWorkbook" object, then in the left combobox, select
"Workbook", in the right combobox select "NewSheet". This should add a
routine that looks like this:
Private Sub Workbook_NewSheet(ByVal Sh As Object)

End Sub

change it to look like this:
Private Sub Workbook_NewSheet(ByVal Sh As Object)
Call YourMacro
End Sub

This will run the macro named "YourMacro" everytime a sheet is added to your
workbook
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top