[Solved] How to automatically rename a sheet after a cell ?

Discuss the spreadsheet application
Post Reply
bouee
Posts: 5
Joined: Mon May 12, 2008 5:48 pm

[Solved] How to automatically rename a sheet after a cell ?

Post by bouee »

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
Last edited by Hagar Delest on Tue Jun 10, 2008 3:40 pm, edited 2 times in total.
Reason: tagged the thread as Solved.
openoffice 2.4, windows xp home sp2
bouee
Posts: 5
Joined: Mon May 12, 2008 5:48 pm

Re: how to automatically re name a sheet after a cell ?

Post by bouee »

here is the answer I got from another Openoffice forum, it works very well, thanks again Mark !!

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
TerryE
Volunteer
Posts: 1402
Joined: Sat Oct 06, 2007 10:13 pm
Location: UK

Re: how to automatically re name a sheet after a cell ?

Post by TerryE »

Try

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
Notes:
  • 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.
jmjohns
Posts: 5
Joined: Tue Mar 01, 2011 8:37 pm

Re: [Solved] How to automatically rename a sheet after a cel

Post by jmjohns »

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
Post Reply