Add additional text within copied sheet name brackets

Hi,

I have 2 scenarios that I am trying to cater for both which work well but I am trying to tweak one to facilitate reporting later on.

The first allows me to copy a sheet to the workbook from a hidden "template" sheet then rename it to the name of the current sheet and then auto number the new sheet in sequence.

Example - Scenario 1
1. Template sheet called My template and hidden
2. Active sheet called (Test)
Code then
Copies a sheet to workbook based on template and rename's it to Test 1
Code again would add template sheet and rename sheet Test 2 etc.

Scenario 2
Using just the ActiveSheet.Copy After:=ActiveSheet allows me to take the active sheet and then in excel fashion add Sheet name and then number in brackets.

This again is fine but i was wondering if there is anyway of having fixed text within the brackets preceeding the number

Example

Active sheet..........Test 1
Result of copy....... Test 1 (1)
Result of another copy...... Test 1 (2)
Etc.

I am trying to to get it so I can have a result like this...

Active sheet..........Test 1
Result of copy....... Test 1 (Option 1)
Result of another copy...... Test 1 (Option 2)
Etc.

The actual copy is perfect but later processes and reporting will be much improved and identified if I could add "Option" within the bracketed number which is being generated by the ActiveSheet.Copy After:=ActiveSheet code.

The other nice thing about this for me is that all sheets are displayed correctly in sequence as the book is built up.

(On site survey form)

I just can't see where I can add the additional text as it seems it's just the way excel deals with the copy and nothing that I can pick apart.

Any thoughts greatly received

Dean

OK,

The code seems to be creating the sheet in a hidden state.
I have just played with creating the sheets and have found the following:

each time the code creates the new copy it appears to be always basing it on the hard coded sheet name (Test 1)

I think I'm right in saying that I have a variant here?

The Hidden template sheet I have is actually called New Location
The code which adds me a New Location takes this hidden sheet and then renames it to the last New location +1.....


Example
Run code on Hidden sheet called "New Location", get new active and visible sheet called Location 1.

Run code again get another new active and visible sheet called Location 2

Etc.

What I'm wanting to do now is duplicate any particular Location sheet that I have created as above and filled out (could be Location 1, could be location 4 etc.

but with this macro, just duplicate the sheet I run it on and have it place the copy of itself to the right of this active sheet

The way Excel name's the sheet during this process duplicates the sheet name in full (which is fine) and then adds the (1), (2), etc. at the end as you run the macro over and over.

This is the bit I am trying to tap into and add the word Option behind excel's end number to my sheet name which it puts in the brackets.


I hope this makes sense.

Deano

Wow,

Getting there.

Sheets now visible but as I just mentioned, the copy is always basing it'self on the hardcoded sheet name.

If I happen to be running this on Location 1 then all is good.

If I then happen to run it on Location 4 say, then I get the next "Option number" but it is still being numbered on Location 1

Thank you so much for your time here I'm really grateful

Regards

Dean