[Dropped] Macro to rename sheet from cell value

[E. Smith]

I am trying to create a macro that will rename the current sheet (Say Sheet 5) to the value of a cell in the current sheet

eg: in Cell A1 I have "2017"
in Cell B1 I have "1" (To represent the month of January)
in cell C1 I have "1" (To represent the 1st day of the month)
in cell D1 I have a Variable such as a,b,c,d etc. (Say I have "a")
in Cell E1 I have all the values above combined to give me a cell value of "1Jan2017a"

I want to be able to run a macro that will rename the current sheet with the value of cell E1, So the result will be a sheet called "1Jan2017a"


I have tried all the macros in this forum for similar topics but they have not worked.

Your help would be appreciated.
Thank-you

[robleyd]

Disclaimer; I don't use macros.

It seems that the solution in viewtopic.php?f=9&t=5693 is almost what you want. Just take out the loop and modify the cell addresses as needed.

[E. Smith]

I tried the editing the script mentioned in the link above but didn't work:

Sub Main
   Dim oSheet
   Dim oCell
   
      oSheet = ThisComponent.Sheets
      oCell = oSheet.getCellRangeByName("A1")
      oSheet.Name = oCell.String
 
End Sub

Any other suggestions?

[Zizi64]

Code: Select all

oSheet = ThisComponent.Sheets

Which sheet? The .Sheets will get ALL of the Sheets of a document. You must choose ONE of them. You can get a sheet by name or by its number or you can get the actual active sheet.

[Villeroy]

Instead of macro programming, you'd better learn how to organize data efficiently so you can use this software (or any other software for that matter) without extra coding.
Getting information from separate lists is very difficult. Keeping multiple lists for the same class of information is one of the most toxic beginner's mistakes.

Look at one of my example spreadsheets:
download/file.php?id=23902

The first sheet named "Data" has 1000 records in random order below a single row of column headers. This is called a "normalized list". Software can deal very well with normalized lists. You can insert a new row anywhere in that list in oder to add a new record. The position does not matter. Insert rows and keep the order of columns. Put dates below dates, values below values, names below names. Just avoid row #2 for insertion. Due to a design flaw, OpenOffice would copy the format attributes from the header row.

The header row includes auto-filter buttons. They help you to filter this list in place in order to see only the records you are interested in. This is way more convenient than finding the right sheet where to add/remove/edit the information. There are other filtering tools where you can specify multiple filter criteria. Sometimes you may want a certain category copied to a separate sheet. Even that is possible.

You can also click any single cell in that list and click one of the sort buttons [A-Z] or [Z-A] in order to sort the whole list by the clicked column.

More importantly a single normalized list allows you to aggregate your data by means of software tools. With a few clicks you can get the sum, count, average, min, max, median for any combination of years, quarters, months, days, weeks, category names or person names as demonstrated by the "Pilot" sheets. "Data Pilots", also known as "Pivot Tables" are described here: https://wiki.openoffice.org/wiki/Docume ... /DataPilot

Filtering, sorting, pivot tables, menu:Data>Subtotals ..., database functions and various other spreadsheet functions (SUMIF, SUBTOTAL, SUMPRODUCT) let you derive information from a normalized list.

[karolus]

Hallo

+1 for Villeroy…

but anyway- assuming cell E1 is already selected:

Code: Select all

sub rename_current_sheet()
	doc = ThisComponent
	cell = doc.CurrentSelection
	sheet = cell.Spreadsheet
	sheet.Name = cell.String	
End Sub

[DiGro]

but anyway- assuming cell E1 is already selected:

but then again - assuming cell E1 is NOT selected:

Code: Select all

sub rename_current_sheet()
Dim oDoc, oSheet, oCell
   oDoc = ThisComponent
   oSheet = oDoc.getCurrentController.getActiveSheet
   oCell = oSheet.getCellByPosition(4,0) 'cell E1
   oSheet.Name = oCell.String 
End Sub

[RoryOF]

Can you really not see the value to change? In the array that is the cells, all values start with 0, so 4,0 is E,1; A=0, B=1, C=2, D=3,E=4 and Row 1 in the spreadsheet maps to row 0 in the internal array.

[E. Smith]

Thanks for Trying But Neither of the two examples above work. I get a Basic Syntax error on line "doc = ThisComponent"

[Zizi64]

Please upload your example .ods file with the embedded macro code here.

[DiGro]

Works for me though

RenameSheet_test.ods

File to test renaming sheet to value in cell E1

(10 KiB) Downloaded 375 times

[Villeroy]

Works for me though

RenameSheet_test.ods

In order to make the green button functional:
View>Toolbars>Form Design
Toggle OFF the 2nd button from the left ("Toggle Design Mode")
Toggle OFF the 4th button from the right ("Open in Design Mode")
Save