Macro for renaming all sheets from a cell

[Michaud79]

Hi there!

I’ve tried writing a macro that kinda works, but only occasionally, and I am stiumped as to why… No error message shows up, and for some reason, some of the sheets get renamed, and some don’t, which I find very weird. It may just be that something very obvious escapes me, as I am new to VBA.

I have a bunch of sheets that I’d like to rename, all at the same time, using the unique value in the B1 cell.

I’ve mostly based my Macro on this excellent document:

LibOBasic-3-Calc-Flat-A4-EN-v111.pdf

Thank you so much for your help!

Here is the code:
Sub Main
Dim Doc as Object
Doc = ThisComponent

	Dim oSheet
	Dim oCell
	
	Dim i
	Dim count
	count = Doc.Sheets.Count-1
	
	For i = 0 to count
		oSheet = Doc.Sheets(i)
		oCell = oSheet.getCellRangeByName("B1")
		oSheet.Name = oCell.String
   Next i
End Sub

[elmau]

Try:

doc = ThisComponent
For Each sheet In doc.Sheets
	sheet.Name = sheet.getCellRangeByName("B1").String
Next

[Michaud79]

Never mind, answered my own question! Your solution is perfect and elegant. Thank you!