Hello again,
this post is almost the same as my previous post, but I have decided to create a new topic since it is slightly different. Another way to solve my problem, would be to automatically re name a sheet after a cell.
My spreadsheet has about 40 sheets, each sheet should have the name of its cell A1
If anbody could help, that would be great !!
thank you
[Solved] How to automatically rename a sheet after a cell ?
[Solved] How to automatically rename a sheet after a cell ?
Last edited by Hagar Delest on Tue Jun 10, 2008 3:40 pm, edited 2 times in total.
Reason: tagged the thread as Solved.
Reason: tagged the thread as Solved.
openoffice 2.4, windows xp home sp2
Re: how to automatically re name a sheet after a cell ?
here is the answer I got from another Openoffice forum, it works very well, thanks again Mark !!
code :
****
You'll just need to change the maximum value of i (don't forget it will need to be one less than the actual number of sheets).
code :
Code: Select all
Sub Main
Dim oSheet
Dim oCell
Dim i
For i = 0 to 2
oSheet = ThisComponent.Sheets(i)
oCell = oSheet.getCellRangeByName("A1")
oSheet.Name = oCell.String
Next i
End Sub
****
You'll just need to change the maximum value of i (don't forget it will need to be one less than the actual number of sheets).
openoffice 2.4, windows xp home sp2
Re: how to automatically re name a sheet after a cell ?
Try
Notes:
Code: Select all
Sub RenameSheets
Dim oSheets, i
oSheets = StarDesktop.CurrentComponent.Sheets
For i = 0 to oSheets.Count-1
oSheets(i).Name = oSheets(i).getCellByPosition(0,0).String
Next i
End Sub
- ThisComponent's use can be a bit fraught is you want to put the macro in somewhere else than the Calc document itself. StarDesktop.CurrentComponent always selects the active document even if the macro is in a Library (say [My Macros & Dialogues].myExtension[/b]).
- I've automated the count limit.
- I've abbreviated the code, dropping some of the intermediate assignments to oSheet and oCell, mainly to show how you can.
- I've used getCellByPosition, but that's a matter of personal preference.
- As I suggested above you can use the Macro Organiser to create or select a module in your My Macros & Dialogues.
- I find it convenient to use the Tools->Customize->Menus dialogue to add my own Utilities menu, and then by selecting it in the Menu, you can use the add browser to select the macro from the My Macros & Dialogues tree. That way you can simply choose Utilities->RenameSheets from any open worksheet.
Ubuntu 11.04-x64 + LibreOffice 3 and MS free except the boss's Notebook which runs XP + OOo 3.3.
Re: [Solved] How to automatically rename a sheet after a cel
I used the first macro in this thread. Is there a way to use a varible number of sheets instead of a fixed number. The number of sheets in my workbook are always changing. In each workbook I have "=sheets()-1" which will give me the new number that needs to go where the 2 is. If this macro worked for just the selected sheet that would be fine too.
OpenOffice 3.2 Windows Vista