renaming worksheet tabs

[william.healy]

Problem: I'm using the INDIRECT function to lookup
information.

I want to mass update the names of 600 worksheets,
contained within 8 Excel Spreadsheets (each worksheet
contains information relating to the "customer account
number")

Any ideas how I can do this "short hand" - my VB skills
are "limited" at best!

 

[Bob Phillips]

Is the new name in a designated cell on each sheet? If so, you could simply
open all of the 8 workbooks in Excel, then use this code

For Each wb In Workbooks
For Each sh In wb.Worksheets
sh.Name = sh.Range("A1")
Next sh
Next wb


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[william.healy]

Fantastic!

It may seem churlish, but anyway this can be worked from
a source table instead?

 

[Bob Phillips]

Do you mean like this

Set rngSheets = ActiveSheet.Range("A1:A600")
i = 1
For Each wb In Workbooks
For Each sh In wb.Worksheets
sh.Name = rngSheets(i, 1).Value
i = i + 1
Next sh
Next wb

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)