[Solved] Rename sheet using a macro

[bsi]

Всем привет. Есть файл, сделанный с макросом в Excek. Необходимо переделать макрос для работы в Calc. Макрос выполняет переименование листов когда его имя изменяю в ярлыке листа или в листе Итог, короче имя листа можно менять и в ярлыке и в листе Итог. И ещё одно. при смене имени ярлыка в листе Итог, автоматически меняется ссылка на ячейку в листе Итог.
Я в макросах мало что понимаю поэтому обращаюсь к специалистам. Всем ответившим огромное спасибо.
Фал приложен.

[Translated by Deepl]

Hello everyone. I have a file created using a macro in Excel. I need to adapt the macro to work in Calc. The macro renames worksheets when their names are changed either in the worksheet tab or in the ‘Summary’ sheet; in short, the worksheet name can be changed both in the tab and in the ‘Summary’ sheet. One more thing: when the label name in the ‘Summary’ sheet is changed, the cell reference in the ‘Summary’ sheet changes automatically.
I don’t know much about macros, so I’m turning to the experts. Many thanks to everyone who replies.
The file is attached.

Для форума.xls

(20.5 KiB) Downloaded 145 times

Edit: There are 13 sheets in the attachment. The first one (Январь) is January. The next 11 are Sheet_2 through Sheet_12. The last one (Итог) is Summary. In the attachment (20992 bytes), the only sheet with data is the last one. -- MrProgrammer, forum moderator

[bsi]

А что ещё надо? К-во листов есть, на листе Январь в I2 ссылка в лист Итог ,есть, макрос написанный для Excel, внутри файла есть, в листе Итог ссылки на первые три листа есть. Прилагаю новый файл.

Для форума.xls

(23 KiB) Downloaded 143 times

[Translation provided from Deepl.com (RoryOF, Moderator)]

What else is needed? I have the worksheets; in the ‘January’ worksheet, cell I2 contains a link to the ‘Total’ worksheet; there is a macro written for Excel, which is included in the file; and in the ‘Total’ worksheet, there are links to the first three worksheets. I’ve attached a new file.

[MrProgrammer]

This is an English speaking forum. So please don't post in your native language:
• You'll get fewer replies because most users will ignore the topic
• The topic indexes will become cluttered with foreign words
• Searches may give topics other users won't be able to understand
Survival Guide → Make your post understandable by others

If you post in Russian again without an English translation, the topic will be locked.
-- MrProgrammer, forum moderator

In the second attachment (23,552 bytes) in sheet Итог cell C2 you could use =IF(ISNUMBER($Январь.I3);$Январь.I3;T($Январь.I3)) instead of =$Январь.I3 to avoid displaying 0 for copies of empty cells, with the same formula pattern for the other cells in columns B, C, D, E.

[MrProgrammer]

The forum's Survival Guide asks you to tell us what version of OpenOffice/LibreOffice you use and what operating system you use. You have not done this, and it prevents me from supplying information appropriate for your environment. Fix your forum signature in User Control Panel → Profile → Edit Signature. I will not respond further until that is updated.

Personally, I would not use separate sheets for each month. As explained in hundreds of topics, this is the wrong approach for Calc. To take advantage of the features in Calc, put all the months in one sheet, adding a column to the sheet which provides the month. Then you can view a specific month with Data → Filter, and all months with Data → Filter → Remove Filter. The Calc Guide and the online help both explain how to use filters if this important feature is new to you. With a single sheet you can use a Pivot Table to summarize the data by month. A Pivot Table is much easier than attempting to perform similar calculations in a Summary sheet with formulas. With all the months in one sheet, the need for your macros vanishes immediately. If you want to struggle with twelve separate sheets, continue reading.

I have a file created using a macro in Excel. I need to adapt the macro to work in Calc. I don’t know much about macros.

You won't need an evil macro in Calc. All you need is the simple 3-line SHEET_NAMES() function, below, in Summary cell A2. Before you can use it you will need to install the function in your OpenOffice system by following the instructions in [Tutorial] How to install a code snippet.

   Function SHEET_NAMES()
   SHEET_NAMES = ThisComponent.Sheets.getElementNames()
   End Function

This is an array function where you type the formula in the cell and press ⇪⌘Enter (Shift+Command+Enter) on a Mac or Ctrl+Shift+Enter on other platforms; if you press Enter by mistake, use Edit → Delete Contents → Formulas, then try again. Since you have 13 sheets, the SHEET_NAMES() function returns a row of 13 sheet names. The INDEX function in the same cell transforms that to a column of the first twelve names. These are stored in Summary as cells A2:A13. I have already done this for you in my 202609041308.ods attachment.

202609041308.ods

(10.3 KiB) Downloaded 125 times

The worksheet name can be changed both in the tab and in the ‘Summary’ sheet.

If you want to change the sheet name, only do it with the sheet tab or with Format → Sheet → Rename. Then the updated reference will appear in the Summary sheet as soon as you use Recalculate All: ⇪⌘F9 (Shift+Command+F9) on a Mac or Ctrl+Shift+F9 on other platforms. Do not attempt to manually update the cells in column A of the Summary sheet since those values are supplied by the SHEET_NAMES() function.

One more thing: when the label name in the ‘Summary’ sheet is changed, the cell reference in the ‘Summary’ sheet changes automatically.

In my attachment you will see that the Summary reference changes automatically based on the sheet name in column A because the formula uses the INDIRECT() function. INDIRECT is Calc function supplied by OpenOffice so it's currently available in your system. When you open my attachment you will probably see the names of functions INDEX, ROW, TRANSPOSE and INDIRECT translated to Russian.

Read about array functions, Pivot Tables, INDEX, ROW, TRANSPOSE, and INDIRECT in Help → Index or in User Guides (PDF) or searching for topics about them in the Calc Forum.

[Tutorial] Ten concepts that every Calc user should know

If this solved your problem please go to your first post use the Edit ✏️ button and add [Solved] to the start of the Subject field. Select the green checkmark icon at the same time.

[Bidouille]

XLS = Microsoft. Basic is not compatible with this foreign format.