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 <users@global.libreoffice.org> From: Tom Cloyd MS MA <tomcloydm...@gmail.com> 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 com.sun.star.beans.PropertyValue args10(0).Name = "Nr" args10(0).Value = 1 dispatcher.executeDispatch(document, ".uno:JumpToTable", "", 0, args10()) rem ---------------------------------------------------------------------- dim args11(5) as new com.sun.star.beans.PropertyValue args11(0).Name = "Flags" args11(0).Value = "SVDFNT" args11(1).Name = "FormulaCommand" args11(1).Value = 0 args11(2).Name = "SkipEmptyCells" args11(2).Value = false args11(3).Name = "Transpose" args11(3).Value = false args11(4).Name = "AsLink" args11(4).Value = false args11(5).Name = "MoveMode" args11(5).Value = 4 dispatcher.executeDispatch(document, ".uno:InsertContents", "", 0, args11()) rem ---------------------------------------------------------------------- dim args12(1) as new com.sun.star.beans.PropertyValue args12(0).Name = "By" args12(0).Value = 1 args12(1).Name = "Sel" args12(1).Value = false dispatcher.executeDispatch(document, ".uno:GoDown", "", 0, args12()) rem ---------------------------------------------------------------------- dim args13(0) as new com.sun.star.beans.PropertyValue args13(0).Name = "Nr" args13(0).Value = 4 dispatcher.executeDispatch(document, ".uno:JumpToTable", "", 0, args13()) rem ---------------------------------------------------------------------- dim args14(0) as new com.sun.star.beans.PropertyValue args14(0).Name = "ToPoint" args14(0).Value = "$A$1:$E$1" dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args9()) rem ---------------------------------------------------------------------- dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array()) rem ---------------------------------------------------------------------- dim args15(0) as new com.sun.star.beans.PropertyValue args15(0).Name = "Nr" args15(0).Value = 1 dispatcher.executeDispatch(document, ".uno:JumpToTable", "", 0, args15()) rem ---------------------------------------------------------------------- dim args16(5) as new com.sun.star.beans.PropertyValue args16(0).Name = "Flags" args16(0).Value = "SVDFNT" args16(1).Name = "FormulaCommand" args16(1).Value = 0 args16(2).Name = "SkipEmptyCells" args16(2).Value = false args16(3).Name = "Transpose" args16(3).Value = false args16(4).Name = "AsLink" args16(4).Value = false args16(5).Name = "MoveMode" args16(5).Value = 4 dispatcher.executeDispatch(document, ".uno:InsertContents", "", 0, args16()) rem ---------------------------------------------------------------------- dim args17(1) as new com.sun.star.beans.PropertyValue args17(0).Name = "By" args17(0).Value = 1 args17(1).Name = "Sel" args17(1).Value = false dispatcher.executeDispatch(document, ".uno:GoDown", "", 0, args17()) rem ---------------------------------------------------------------------- dim args20(0) as new com.sun.star.beans.PropertyValue args20(0).Name = "Nr" args20(0).Value = 5 dispatcher.executeDispatch(document, ".uno:JumpToTable", "", 0, args20()) rem ---------------------------------------------------------------------- dim args21(0) as new com.sun.star.beans.PropertyValue args21(0).Name = "ToPoint" args21(0).Value = "$A$1:$E$1" dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args21()) rem ---------------------------------------------------------------------- dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array()) rem ---------------------------------------------------------------------- dim args22(0) as new com.sun.star.beans.PropertyValue args22(0).Name = "Nr" args22(0).Value = 1 dispatcher.executeDispatch(document, ".uno:JumpToTable", "", 0, args22()) rem ---------------------------------------------------------------------- dim args23(5) as new com.sun.star.beans.PropertyValue args23(0).Name = "Flags" args23(0).Value = "SVDFNT" args23(1).Name = "FormulaCommand" args23(1).Value = 0 args23(2).Name = "SkipEmptyCells" args23(2).Value = false args23(3).Name = "Transpose" args23(3).Value = false args23(4).Name = "AsLink" args23(4).Value = false args23(5).Name = "MoveMode" args23(5).Value = 4 dispatcher.executeDispatch(document, ".uno:InsertContents", "", 0, args23()) rem ---------------------------------------------------------------------- dim args24(1) as new com.sun.star.beans.PropertyValue args24(0).Name = "By" args24(0).Value = 1 args24(1).Name = "Sel" args24(1).Value = false dispatcher.executeDispatch(document, ".uno:GoDown", "", 0, args24()) rem ---------------------------------------------------------------------- dim args30(0) as new com.sun.star.beans.PropertyValue args30(0).Name = "Nr" args30(0).Value = 6 dispatcher.executeDispatch(document, ".uno:JumpToTable", "", 0, args30()) rem ---------------------------------------------------------------------- dim args31(0) as new com.sun.star.beans.PropertyValue args31(0).Name = "ToPoint" args31(0).Value = "$A$1:$E$1" dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args31()) rem ---------------------------------------------------------------------- dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array()) rem ---------------------------------------------------------------------- dim args32(0) as new com.sun.star.beans.PropertyValue args32(0).Name = "Nr" args32(0).Value = 1 dispatcher.executeDispatch(document, ".uno:JumpToTable", "", 0, args32()) rem ---------------------------------------------------------------------- dim args33(5) as new com.sun.star.beans.PropertyValue args33(0).Name = "Flags" args33(0).Value = "SVDFNT" args33(1).Name = "FormulaCommand" args33(1).Value = 0 args33(2).Name = "SkipEmptyCells" args33(2).Value = false args33(3).Name = "Transpose" args33(3).Value = false args33(4).Name = "AsLink" args33(4).Value = false args33(5).Name = "MoveMode" args33(5).Value = 4 dispatcher.executeDispatch(document, ".uno:InsertContents", "", 0, args33()) rem ---------------------------------------------------------------------- dim args34(1) as new com.sun.star.beans.PropertyValue args34(0).Name = "By" args34(0).Value = 1 args34(1).Name = "Sel" args34(1).Value = false dispatcher.executeDispatch(document, ".uno:GoDown", "", 0, args34()) rem ---------------------------------------------------------------------- dim args40(0) as new com.sun.star.beans.PropertyValue args40(0).Name = "Nr" args40(0).Value = 7 dispatcher.executeDispatch(document, ".uno:JumpToTable", "", 0, args40()) rem ---------------------------------------------------------------------- dim args41(0) as new com.sun.star.beans.PropertyValue args41(0).Name = "ToPoint" args41(0).Value = "$A$1:$E$1" dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args41()) rem ---------------------------------------------------------------------- dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array()) rem ---------------------------------------------------------------------- dim args42(0) as new com.sun.star.beans.PropertyValue args42(0).Name = "Nr" args42(0).Value = 1 dispatcher.executeDispatch(document, ".uno:JumpToTable", "", 0, args42()) rem ---------------------------------------------------------------------- dim args43(5) as new com.sun.star.beans.PropertyValue args43(0).Name = "Flags" args43(0).Value = "SVDFNT" args43(1).Name = "FormulaCommand" args43(1).Value = 0 args43(2).Name = "SkipEmptyCells" args43(2).Value = false args43(3).Name = "Transpose" args43(3).Value = false args43(4).Name = "AsLink" args43(4).Value = false args43(5).Name = "MoveMode" args43(5).Value = 4 dispatcher.executeDispatch(document, ".uno:InsertContents", "", 0, args43()) rem ---------------------------------------------------------------------- dim args44(1) as new com.sun.star.beans.PropertyValue args44(0).Name = "By" args44(0).Value = 1 args44(1).Name = "Sel" args44(1).Value = false dispatcher.executeDispatch(document, ".uno:GoDown", "", 0, args44()) rem ---------------------------------------------------------------------- dim args50(0) as new com.sun.star.beans.PropertyValue args50(0).Name = "Nr" args50(0).Value = 8 dispatcher.executeDispatch(document, ".uno:JumpToTable", "", 0, args50()) rem ---------------------------------------------------------------------- dim args51(0) as new com.sun.star.beans.PropertyValue args51(0).Name = "ToPoint" args51(0).Value = "$A$1:$E$1" dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args51()) rem ---------------------------------------------------------------------- dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array()) rem ---------------------------------------------------------------------- dim args52(0) as new com.sun.star.beans.PropertyValue args52(0).Name = "Nr" args52(0).Value = 1 dispatcher.executeDispatch(document, ".uno:JumpToTable", "", 0, args52()) rem ---------------------------------------------------------------------- dim args53(5) as new com.sun.star.beans.PropertyValue args53(0).Name = "Flags" args53(0).Value = "SVDFNT" args53(1).Name = "FormulaCommand" args53(1).Value = 0 args53(2).Name = "SkipEmptyCells" args53(2).Value = false args53(3).Name = "Transpose" args53(3).Value = false args53(4).Name = "AsLink" args53(4).Value = false args53(5).Name = "MoveMode" args53(5).Value = 4 dispatcher.executeDispatch(document, ".uno:InsertContents", "", 0, args53()) rem ---------------------------------------------------------------------- dim args54(1) as new com.sun.star.beans.PropertyValue args54(0).Name = "By" args54(0).Value = 1 args54(1).Name = "Sel" args54(1).Value = false dispatcher.executeDispatch(document, ".uno:GoDown", "", 0, args54()) rem ---------------------------------------------------------------------- dim args60(0) as new com.sun.star.beans.PropertyValue args60(0).Name = "Nr" args60(0).Value = 9 dispatcher.executeDispatch(document, ".uno:JumpToTable", "", 0, args60()) rem ---------------------------------------------------------------------- dim args61(0) as new com.sun.star.beans.PropertyValue args61(0).Name = "ToPoint" args61(0).Value = "$A$1:$E$1" dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args61()) rem ---------------------------------------------------------------------- dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array()) rem ---------------------------------------------------------------------- dim args62(0) as new com.sun.star.beans.PropertyValue args62(0).Name = "Nr" args62(0).Value = 1 dispatcher.executeDispatch(document, ".uno:JumpToTable", "", 0, args62()) rem ---------------------------------------------------------------------- dim args63(5) as new com.sun.star.beans.PropertyValue args63(0).Name = "Flags" args63(0).Value = "SVDFNT" args63(1).Name = "FormulaCommand" args63(1).Value = 0 args63(2).Name = "SkipEmptyCells" args63(2).Value = false args63(3).Name = "Transpose" args63(3).Value = false args63(4).Name = "AsLink" args63(4).Value = false args63(5).Name = "MoveMode" args63(5).Value = 4 dispatcher.executeDispatch(document, ".uno:InsertContents", "", 0, args63()) rem ---------------------------------------------------------------------- dim args64(1) as new com.sun.star.beans.PropertyValue args64(0).Name = "By" args64(0).Value = 1 args64(1).Name = "Sel" args64(1).Value = false dispatcher.executeDispatch(document, ".uno:GoDown", "", 0, args64()) rem ---------------------------------------------------------------------- dim args70(0) as new com.sun.star.beans.PropertyValue args70(0).Name = "Nr" args70(0).Value = 10 dispatcher.executeDispatch(document, ".uno:JumpToTable", "", 0, args70()) rem ---------------------------------------------------------------------- dim args71(0) as new com.sun.star.beans.PropertyValue args71(0).Name = "ToPoint" args71(0).Value = "$A$1:$E$1" dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args71()) rem ---------------------------------------------------------------------- dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array()) rem ---------------------------------------------------------------------- dim args72(0) as new com.sun.star.beans.PropertyValue args72(0).Name = "Nr" args72(0).Value = 1 dispatcher.executeDispatch(document, ".uno:JumpToTable", "", 0, args72()) rem ---------------------------------------------------------------------- dim args73(5) as new com.sun.star.beans.PropertyValue args73(0).Name = "Flags" args73(0).Value = "SVDFNT" args73(1).Name = "FormulaCommand" args73(1).Value = 0 args73(2).Name = "SkipEmptyCells" args73(2).Value = false args73(3).Name = "Transpose" args73(3).Value = false args73(4).Name = "AsLink" args73(4).Value = false args73(5).Name = "MoveMode" args73(5).Value = 4 dispatcher.executeDispatch(document, ".uno:InsertContents", "", 0, args73()) rem ---------------------------------------------------------------------- dim args74(1) as new com.sun.star.beans.PropertyValue args74(0).Name = "By" args74(0).Value = 1 args74(1).Name = "Sel" args74(1).Value = false dispatcher.executeDispatch(document, ".uno:GoDown", "", 0, args74()) rem ---------------------------------------------------------------------- dim args80(0) as new com.sun.star.beans.PropertyValue args80(0).Name = "Nr" args80(0).Value = 11 dispatcher.executeDispatch(document, ".uno:JumpToTable", "", 0, args80()) rem ---------------------------------------------------------------------- dim args81(0) as new com.sun.star.beans.PropertyValue args81(0).Name = "ToPoint" args81(0).Value = "$A$1:$E$1" dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args81()) rem ---------------------------------------------------------------------- dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array()) rem ---------------------------------------------------------------------- dim args82(0) as new com.sun.star.beans.PropertyValue args82(0).Name = "Nr" args82(0).Value = 1 dispatcher.executeDispatch(document, ".uno:JumpToTable", "", 0, args82()) rem ---------------------------------------------------------------------- dim args83(5) as new com.sun.star.beans.PropertyValue args83(0).Name = "Flags" args83(0).Value = "SVDFNT" args83(1).Name = "FormulaCommand" args83(1).Value = 0 args83(2).Name = "SkipEmptyCells" args83(2).Value = false args83(3).Name = "Transpose" args83(3).Value = false args83(4).Name = "AsLink" args83(4).Value = false args83(5).Name = "MoveMode" args83(5).Value = 4 dispatcher.executeDispatch(document, ".uno:InsertContents", "", 0, args83()) rem ---------------------------------------------------------------------- dim args84(1) as new com.sun.star.beans.PropertyValue args84(0).Name = "By" args84(0).Value = 1 args84(1).Name = "Sel" args84(1).Value = false dispatcher.executeDispatch(document, ".uno:GoDown", "", 0, args84()) end sub > > Thanks for any ideas offered! > > -- > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ > “Love recognizes no barriers. It jumps hurdles, leaps fences, penetrates walls > to arrive at its destination full of hope.” ~ Maya Angelou > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ > Tom Cloyd, MS MA LMHC (WA) | t...@tomcloyd.com > Psychological trauma & dissociative disorders, treatment, research, & advocacy > Spokane, Washington, U.S.A. | (435) 272-3332 > https://www.gettraumainfo.com/ (professional) > Facebook: https://www.facebook.com/groups/645665272216298/ > www.tomcloyd.com/ (personal) > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ > System76 Galago Pro (galp5) laptop > Processors: 8 × 11th Gen Intel® Core™ i7-1165G7 @ 2.80GHz > RAM: 16 GB Dual Channel DDR4 at 3200 MHz > Storage: 500 GIB SSD > Operating System: Pop!_OS 22.04 LTS > Kernel Version: 6.2.6-76060206-generic x86_64 > Gnome ver. 42.5 > Windowing system: X11 > Qt Version: 5.13.3 > [updated 2023-05-09:1420] > > > -- > To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org > Problems? > https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ > Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette > List archive: https://listarchives.libreoffice.org/global/users/ > Privacy Policy: https://www.documentfoundation.org/privacy +------------------------------------------------------------+ Michael D. Setzer II - Computer Science Instructor (Retired) mailto:mi...@guam.net mailto:msetze...@gmail.com Guam - Where America's Day Begins G4L Disk Imaging Project maintainer http://sourceforge.net/projects/g4l/ +------------------------------------------------------------+ -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette List archive: https://listarchives.libreoffice.org/global/users/ Privacy Policy: https://www.documentfoundation.org/privacy