We will be migrating from Ask to Discourse on the first week of August, read the details here

# Calc macro: loop until only one sheet is left

Hello all,

I have a macro I previously created to copy multiple sheets' data into one, and would like to have it loop, but can't figure it out. Hopefully you can help.

The basic rundown of the macro is this:

• There is always a sheet named Sheet1 at the beginning of the document.
• There are several following sheets with data.
• The macro selects the data from the sheet I am in, cuts it, pastes it into Sheet1, deletes the original sheet, and moves on to the next.

Currently, I am having to run the macro for every sheet until I am down to Sheet1. I want the macro to loop through them automatically until the only sheet remaining is Sheet1 and the rest have been copied/deleted.

Here is the current macro:

REM  *****  BASIC  *****

sub Main
rem ----------------------------------------------------------------------
rem define variables
dim document   as object
dim dispatcher as object
rem ----------------------------------------------------------------------
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 = "Sel"
args1(0).Value = false

dispatcher.executeDispatch(document, ".uno:GoToStart", "", 0, args1())

rem ----------------------------------------------------------------------
dim args2(0) as new com.sun.star.beans.PropertyValue
args2(0).Name = "Sel"
args2(0).Value = true

dispatcher.executeDispatch(document, ".uno:GoToEndOfData", "", 0, args2())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:Cut", "", 0, Array())

rem ----------------------------------------------------------------------
dim args4(0) as new com.sun.star.beans.PropertyValue
args4(0).Name = "ToPoint"
args4(0).Value = "$'Sheet1'.$A$1" dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args4()) rem ---------------------------------------------------------------------- dim args5(0) as new com.sun.star.beans.PropertyValue args5(0).Name = "Sel" args5(0).Value = false dispatcher.executeDispatch(document, ".uno:GoToEndOfData", "", 0, args5()) rem ---------------------------------------------------------------------- dim args6(1) as new com.sun.star.beans.PropertyValue args6(0).Name = "By" args6(0).Value = 1 args6(1).Name = "Sel" args6(1).Value = false dispatcher.executeDispatch(document, ".uno:GoDown", "", 0, args6()) rem ---------------------------------------------------------------------- dim args7(0) as new com.sun.star.beans.PropertyValue args7(0).Name = "Sel" args7(0).Value = false dispatcher.executeDispatch(document, ".uno:GoToStartOfRow", "", 0, args7()) rem ---------------------------------------------------------------------- dispatcher.executeDispatch(document, ".uno:Paste", "", 0, Array()) rem ---------------------------------------------------------------------- dispatcher.executeDispatch(document, ".uno:JumpToNextTable", "", 0, Array()) rem ---------------------------------------------------------------------- dispatcher.executeDispatch(document, ".uno:Remove", "", 0, Array()) end sub  Any help would be appreciated. Thanks! edit retag close merge delete ## 1 Answer Sort by » oldest newest most voted Hello @lgpa To loop through sheets, you just need to know how many loops to perform. Count property of ThisComponent.Sheets object returns total count of sheets in your document. You can use this number to define how many loops shall be executed: iSheetsCount = ThisComponent.Sheets.Count ' Get total sheets count For i=1 to iSheetsCount-1 ' how many times loop shall be executed ' your code here Next i  Your whole routine can look like this: Sub CopyDeleteSheets dim document as object dim dispatcher as object 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 = "Sel" args1(0).Value = false rem ---------------------------------------------------------------------- dim args2(0) as new com.sun.star.beans.PropertyValue args2(0).Name = "Sel" args2(0).Value = true rem ---------------------------------------------------------------------- dim args4(0) as new com.sun.star.beans.PropertyValue args4(0).Name = "ToPoint" args4(0).Value = "$'Sheet1'.$A$1"
rem ----------------------------------------------------------------------
dim args5(0) as new com.sun.star.beans.PropertyValue
args5(0).Name = "Sel"
args5(0).Value = false
rem ----------------------------------------------------------------------
dim args6(1) as new com.sun.star.beans.PropertyValue
args6(0).Name = "By"
args6(0).Value = 1
args6(1).Name = "Sel"
args6(1).Value = false
rem ----------------------------------------------------------------------
dim args7(0) as new com.sun.star.beans.PropertyValue
args7(0).Name = "Sel"
args7(0).Value = false

iSheetsCount = ThisComponent.Sheets.Count

For i=1 to iSheetsCount-1
ThisComponent.CurrentController.setActiveSheet(ThisComponent.Sheets(1))
dispatcher.executeDispatch(document, ".uno:GoToStart", "", 0, args1())
dispatcher.executeDispatch(document, ".uno:GoToEndOfData", "", 0, args2())
dispatcher.executeDispatch(document, ".uno:Cut", "", 0, Array())
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args4())
dispatcher.executeDispatch(document, ".uno:GoToEndOfData", "", 0, args5())
dispatcher.executeDispatch(document, ".uno:GoDown", "", 0, args6())
dispatcher.executeDispatch(document, ".uno:GoToStartOfRow", "", 0, args7())
dispatcher.executeDispatch(document, ".uno:Paste", "", 0, Array())
dispatcher.executeDispatch(document, ".uno:JumpToNextTable", "", 0, Array())
dispatcher.executeDispatch(document, ".uno:Remove", "", 0, Array())
Next i

End Sub


Anyway, while you can use macro recorder for some simple tasks, it's functionality is limited and if you expect some more complicated tasks in the future, you should consider learning common programming approach.

more

Thank you so much! This works perfectly.

You're totally right about learning more programming. I've been working on it as I need it and have time, but this one I needed done fairly quickly and my typical approach hadn't gotten me there fast enough. Macro recorder works as a quick hack-together method, but as you said, it can't handle some of the more complex features I'm starting to use.

( 2019-11-19 16:45:01 +0200 )edit

Good luck with learning then! And feel free to ask if any support needed, the community here will be glad to help!

( 2019-11-19 21:42:27 +0200 )edit