Need VBA formula that will link to same worksheet even if sheet name changes

[Ochimus]

NOTE TO MODERATORS: I KNOW the Code element below is not in echelons. if I put them in. your SUCURI Firewall blocks the post! If someone can explain how to get round this I would be extremely grateful.

Going round in circles here, so hope someone can help.

User enters the number of worksheets to be created.
Macro creates that number of sheets, and names each one as "(whatever) & i" (where i is the current number created).
Macro then adds a column to the SUMMARY sheet for each new sheet created.
Works perfectly

Challenge: Each worksheet will be filled in, creating a series of subtotals.
I need VBA to produce "formulae" on the SUMMARY sheet that link to the relevant worksheet and cell range automatically, even if the User change the sheet name at some point

I tried setting each new sheet as "ws3" when it was created and used that in the "formulae" (e.g


Range("C6").Select
ActiveCell = "=" & sh3.Name & "!F6:F9"

But every time the Code adds a new sheet, sh3 becomes the new "Active" sheet, which changed the formulae on all the previous columns.

Any suggestions. pointers or alternatives welcome as ever.

Ochimus

[mrice]

You could try using the code name for the sheet e.g. Sheet1. This is the value that appears in the top left hand pane of the VBA editor.

[Logit]

.
Try Sheet1 ... Sheet2 ... Sheet3 etc.

In the VBE you see Sheet3(Sheet3) ... or you may see Sheet3(sh3) if the tab has been named sh3.
In VBA it would be referred to as Sheets("Sheet3") ... or Sheets("sh3") if the tab was renamed.

Using Sheet3 refers to the left side of the sheet name ... Sheet3(Sheet3) ... which doesn't change.

Hope that helps ?

[Ochimus]

Many thanks to both for the prompt responses, but need some clarification?

How do I write the Code to "see" the new sheet number as it's created?

It is almost certainly not going to be sequential, as the "real" file jumps from sheet9 to sheet62 already!

Ochimus

[Logit]

.
Best to post your workbook with the code. Do not include any confidential information.

[Ochimus]

No idea what happened the first time I ran the Code, but I retried the "make the active sheet sh3" approach yesterday, and it didn't "reset" the formulae in the previous columns of the Summary sheet.

So I can mark this as "solved", but grateful for the prompt responses from both of you.

Ochimus

[Logit]

.
Glad you have it solved.