Dynamically change SheetName to a Linked Cell 2

[Wendyj]

Here is the situation:

Excel 97 SR-2 (I know I know)

Workbook consists of

2 externally linked worksheets that will update from the parent worksheet as it opens (faster update than if each worksheet is referencing an external link)

200 worksheets linked to the 2 externally linked worksheets

I want these 200 worksheets (and only these two) to have their SheetNames update dynamically as soon as the workbooks open. The SheetName should update from a cell within each workbook. This cell is linked to one of the 2 externally linked worksheets.

I hope I wrote this clearly, let me know if I've just written jibberish

 

[xlbo]

Can only update a sheet name via code

Activesheet.name = range("A1&quot.text

would work....

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[Wendyj]

This has worked! Thanks a lot

Now my next problem is to have the 200 sheets all change at the same time while NOT changing the 2 externally linked sheets!

 

[xlbo]

For each sht in thisworkbook.worksheets
if sht.name <> &quot;Sheet To Exclude&quot; and sht.name <>&quot;Other Sheet To Exclude&quot; then
sht.name = sht.range(&quot;A1&quot.text
else
end if
Next

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[Wendyj]

I think this may do it. I have to make a couple of small changes but early indications are that this is solved


Thankyou, Thankyou, Thankyou

 

[Wendyj]

It turns out I'm still having a problem.

The error that keeps coming up is

Run Time Error &quot;1004'

Cannot rename a sheet to the same name as another sheet, a referenced object library or a workbook referenced by Visual Basic

The 2 excluded sheets and the first sheet to be renamed go fine, it's the sheets after that. I have triple checked all the referenced cells to make sure that there would be no name violations. I think it is trying to name the following sheets the same name as the first renamed sheet? I have even copy/pasted the names on the sheet tabs to make sure that I have not accidently double named something.

Any thoughts that will help

 

[xlbo]

can you post the code you are using to rename the sheets please

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[Wendyj]

Problem is solved. I just left out the sht. in front of range.

 

[xlbo]

aaaaaah - thought it might be something like that. It would, in that case, be trying to name all the sheets the same as the 1st sheet. Glad you solved it

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244