Macro to write code to a sheet

[Dlaczegoznak]

Hello,

I am self taught with VBA - been using it for a couple of years now, mainly focusing on writing macros into modules. I haven't really touched on writing code into individual sheets before

At the moment I am trying to set up macros to format my worksheets, and part of that is code that will update the formatting whenever the sheet is selected. I have been able to write the sub that I need within individual worksheets (i.e. private sub worksheet_activate()), but ideally I would like to be able to have this same code automatically inserted when I use the first macro which creates / formats the worksheet in the first place. Is there a way to make this happen?

Up till now the best solution I can think of is to have a blank 'sheet 1' with the code in it and have my macros start by copying that sheet and going from there, but it seems a little awkward. Any suggestions will be gratefully accepted.

My current process (in case it helps to clarify what I want):
- create new Excel workbook
- use a macro which inserts the basic worksheets I will use on all workbooks, as well as a sheet which includes basic information needed for most/all worksheets
- use a further macros for the sheets which I will need in this specific workbook as and when I need them

So ideally I would like code to be inserted at the sheet level automatically, how can this happen?

Thanks

[Richard Buttrey]

Have you considered creating a Workbook Template .xltm that contains all your sheets/formatting/macros and saving it in your default Template folder.

When you open a template, add stuff and then choose to save it, it defaults to a .xlsm file and means your template will not be changed and ready to open again.

[Dlaczegoznak]

Thanks for the idea - in this scenario would it be a case of writing the code into the first worksheet and then copying that worksheet every time I need a new worksheet?

By the way, I understand that worksheet_activate() only works when it is in the specific worksheet it refers to (when I've tested it elsewhere it doesn't seem to do anything) - is that correct, or is it possible to have a macro that would apply to all worksheets on activation?

Thanks again

[Richard Buttrey]

If I understand your requirement, which is to have code attached to a sheet replicated in subsequent sheets then typically you'd have a line of macro code at Module level to create a copy of the base sheet. e.g.

Sheet1.Copy after:=Sheets(Sheets.Count)

Note Sheet1 here is the VBA sheet code name NOT the sheet tab name which might als happen to be "Sheet1". ALway use VBA code names in preference to tab names which are too easily changed by the user.

If you wanted to add several sheets you could use a loop and prompt with an INPUT box asking the user how many and use the Input box value as the loop counter.

Just as an aside, it's rarely necessary to use Sheet Activate syntax, or indeed Range Select stuff to work with the objects. Just address the objects directly. Activating or Selecting just slows things down since it requires VBA to jump back to the Excel App which carries a time penalty.