[Solved] Rename worksheets from sheet cells

[Fitch]

Edit: Split from [Solved] Rename worksheet from the cell since this is a different question in a solved topic from a new person

Even newer than "a70Coupe" as far as macros are concerned.
It does work very well.
Thanks for that.
I'm amazed I managed to "make" a macro..
Can I put a spanner in the works and ask if it could name all the sheets at once, or is that a bit too much?
Thanks.

[FJCC]

All of the sheets can be renamed. From where do you want to get the new names?

[Fitch]

From C1 in each sheet.
I have 5 sheets at the moment, but more could follow, so it would have to count the number of sheets, an then rename each sheet to C1 of that sheet.
A bit of a tall order, but it would be handy.
Fitch.

[FJCC]

Here is one solution.

Code: Select all

Sub Rename
oSheets = ThisComponent.Sheets
Cnt = oSheets.Count
For i = 0 to Cnt - 1
  oSheet = oSheets.getByIndex(i)
  oCell = oSheet.getCellrangeByName("C1")
  oSheet.Name = oCell.String
 Next i
End Sub

[Fitch]

Wow!
That worked a treat.
Thank

[karolus]

Hallo

for each exists!

Code: Select all

Sub rename
sheets = thisComponent.Sheets
for each sheet in sheets  
  cell = sheet.getCellrangeByName("C1")
  sheet.Name = cell.String
next 
End Sub

[Villeroy]

You may also select any cell with a sheet name, then all the sheets with sheet names in the same cell and run this:

Code: Select all

addr = ThisComponent.CurrentSelection.getRangeAddresses()
sheets = ThisComponent.getSheets()
for each a in addr
  sh = sheets.getByIndex(a.Sheet)
  c = sh.getCellByPosition(a.StartColumn, a.StartRow)
  sh.setName(c.getString())
next

[Fitch]

I see there's more than 1 way to skin a cat!

Thanks all.