Page 1 of 1
[Solved] Finding the last sheet number with a macro for Calc
Posted: Tue Dec 30, 2014 3:55 pm
by Robitron
I'm writing macros that need to find all sheets and if each sheet is not of a specified name (provided by each macro), then it will automatically hide it. The purpose of this is to hide all irrelevant sheets for the tasks I'm doing at a given time. For instance, if I am working on the expense journal sheet, then the other sheets, not related to expenses would be hidden and those sheets that are related to expenses would be shown.
However, I can not figure out from the basic programmer's guide how to find the number of the last sheet.
This is a macro for managing a chicken farm with mulltiple flocks. One macro adds new sheets everytime a new flock is added. Therefore, the number of sheets is always changing, so manually counting the sheets to get the total number is not the answer.
I'm sure there is a very simple answer but I just can not find it.
Re: Finding the last sheet number with a macro for calc
Posted: Tue Dec 30, 2014 4:14 pm
by FJCC
The Sheets container has a Count property
Code: Select all
oSheets = ThisComponent.Sheets
NumSheets = oSheets.Count
The index of the last sheet would be NumSheets - 1.
Re: Finding the last sheet number with a macro for calc :Sal
Posted: Tue Dec 30, 2014 4:21 pm
by Robitron
Sweet! I knew it was something simple that I was overlooking! Thanks so much!!!
Re: [Solved] Finding the last sheet number with a macro for
Posted: Fri Mar 02, 2018 12:55 am
by UncleCrust
Sorry. I've never made/used a macro before. I've read about recording macros, but how do you stick code into a macro? How does one put a macro into the formula for a cell? I wanted to use the number of sheets value to do calculations from collected data from various sheets.
Re: [Solved] Finding the last sheet number with a macro for
Posted: Fri Mar 02, 2018 10:10 pm
by Lupp
UncleCrust wrote:How does one put a macro into the formula for a cell? I wanted to use the number of sheets value to do calculations from collected data from various sheets.
To calculate something depending on values contained or precalculated in cells of one or more sheets the appropriate means is to reference the respectiv cells in formulas either one by one, by ranges, or even by cuboids depending on the functions to apply. This is basically the way to use spreadsheets. For what resons do you think to need "macros"?
Re: [Solved] Finding the last sheet number with a macro for
Posted: Fri Mar 02, 2018 10:28 pm
by Villeroy
Code: Select all
Function SheetNames()
SheetNames = ThisComponent.Sheets.getElementNames()
End Function
Save this in some module in "My Macros", library "Standard".
Now =SHEETNAMES() (entered as an array formula) returns a list of this document's sheet names.
=INDEX(SHEETNAMES();SHEET()) returns this sheet's name.
=COUNTA(SHEETNAMES()) returns the count of sheets.
=ISNUMBER(MATCH(A1;SHEETNAMES();0)) returns if a sheet name already exists.