On 19 Sep 2023 at 23:06, Tom Cloyd MS MA wrote:
Date sent: Tue, 19 Sep 2023 23:06:41 -0700
To: LibreOffice User's Help Forum
From: Tom Cloyd MS MA
Subject:[libreoffice-users] Calc: how to
auto-increment sheet names in a cell formula?
> I have trying to solve this problem all evening, with no hint of success:
>
> Let's say I have 11 sheets in a Calc spreadsheet. Sheets 2 to 11 contain
> data summarized in cells A1:E1 of each sheet.
>
> I want to capture these summary data in sheet 1, starting with cell
> A1:E1. But I then want to copy those cells into the next row and have
> the references point to the next SHEET - sheet 3.
>
> The general problem is that I have a number of sheets from which I want
> to capture such summary data, but without having to manually edit the
> cell formulas each time to get them to point to the next sheet.
>
> Incidentally, the sheet names are NOT sheet1, sheet2, etc. So moving to
> the next sheet needs to use some general method of incrementing the
> sheet reference.
>
> Is there an artful way to do this?
Not 100% sure I understand exactly what you want, but did a test
macro that does what I think you want. Isn't complex, but mainly a
process. The Macro uses Page numbers, so it doesn't care what the
Sheets names are:
Recorded manual process first. Had macro go to cell A30
Then had it copy a1:e1 on second sheet, and go down.
Then copied the lines.
First couple copied the macro lines manually, and modified
numbers for each arg one by one. Then got smart, and changed
numbers in blocks of 10 to make it simplier. Just copied the block
in geany and then changed page number to go to, and then
changed numbers in next block. Should be obvious.
Only thing might add is to clear the lines on sheet1 since second or
later times, it gives pop up message since pasting is overwriting
data.
sub multisheet2
rem --
rem define variables
dim document as object
dim dispatcher as object
rem --
rem get access to the document
document = ThisComponent.CurrentController.Frame
dispatcher =
createUnoService("com.sun.star.frame.DispatchHelper")
rem --
dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "ToPoint"
args1(0).Value = "$A$30"
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0,
args1())
rem --
dim args2(0) as new com.sun.star.beans.PropertyValue
args2(0).Name = "Nr"
args2(0).Value = 2
dispatcher.executeDispatch(document, ".uno:JumpToTable", "", 0,
args2())
rem --
dim args3(0) as new com.sun.star.beans.PropertyValue
args3(0).Name = "ToPoint"
args3(0).Value = "$A$1:$E$1"
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0,
args3())
rem --
dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array())
rem --
dim args5(0) as new com.sun.star.beans.PropertyValue
args5(0).Name = "Nr"
args5(0).Value = 1
dispatcher.executeDispatch(document, ".uno:JumpToTable", "", 0,
args5())
rem --
dim args6(5) as new com.sun.star.beans.PropertyValue
args6(0).Name = "Flags"
args6(0).Value = "SVDFNT"
args6(1).Name = "FormulaCommand"
args6(1).Value = 0
args6(2).Name = "SkipEmptyCells"
args6(2).Value = false
args6(3).Name = "Transpose"
args6(3).Value = false
args6(4).Name = "AsLink"
args6(4).Value = false
args6(5).Name = "MoveMode"
args6(5).Value = 4
dispatcher.executeDispatch(document, ".uno:InsertContents", "", 0,
args6())
rem --
dim args7(1) as new com.sun.star.beans.PropertyValue
args7(0).Name = "By"
args7(0).Value = 1
args7(1).Name = "Sel"
args7(1).Value = false
dispatcher.executeDispatch(document, ".uno:GoDown", "", 0,
args7())
rem --
dim args8(0) as new com.sun.star.beans.PropertyValue
args8(0).Name = "Nr"
args8(0).Value = 3
dispatcher.executeDispatch(document, ".uno:JumpToTable", "", 0,
args8())
rem --
dim args9(0) as new com.sun.star.beans.PropertyValue
args9(0).Name = "ToPoint"
args9(0).Value = "$A$1:$E$1"
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0,
args9())
rem --
dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array())
rem --
dim args10(0) as new
