Loop macro actions

English
, , , , ,
#1

Hi everyone, I want to loop the actions I recorded on a macro.
The macro does as follows:

Copy the cells N24:N35 from Sheet1

Paste transposed in a second sheet of the document

Insert one row above the pasted row

Delete cells N24:N35 from Sheet1 so the new values can be copied and pasted.

I want to loop that macro run 400 times.

Can someone please help me to figure out how to do it?

The macro looks like this:

REM  *****  BASIC  *****
   
sub Main
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 = "$N$24"

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

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

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

rem ----------------------------------------------------------------------
dim args3(0) as new com.sun.star.beans.PropertyValue
args3(0).Name = "By"
args3(0).Value = 1

dispatcher.executeDispatch(document, ".uno:GoDownSel", "", 0, args3())

rem ----------------------------------------------------------------------
dim args4(0) as new com.sun.star.beans.PropertyValue
args4(0).Name = "By"
args4(0).Value = 1

dispatcher.executeDispatch(document, ".uno:GoDownSel", "", 0, args4())

rem ----------------------------------------------------------------------
dim args5(0) as new com.sun.star.beans.PropertyValue
args5(0).Name = "By"
args5(0).Value = 1

dispatcher.executeDispatch(document, ".uno:GoDownSel", "", 0, args5())

rem ----------------------------------------------------------------------
dim args6(0) as new com.sun.star.beans.PropertyValue
args6(0).Name = "By"
args6(0).Value = 1

dispatcher.executeDispatch(document, ".uno:GoDownSel", "", 0, args6())

rem ----------------------------------------------------------------------
dim args7(0) as new com.sun.star.beans.PropertyValue
args7(0).Name = "By"
args7(0).Value = 1

dispatcher.executeDispatch(document, ".uno:GoDownSel", "", 0, args7())

rem ----------------------------------------------------------------------
dim args8(0) as new com.sun.star.beans.PropertyValue
args8(0).Name = "By"
args8(0).Value = 1

dispatcher.executeDispatch(document, ".uno:GoDownSel", "", 0, args8())

rem ----------------------------------------------------------------------
dim args9(0) as new com.sun.star.beans.PropertyValue
args9(0).Name = "By"
args9(0).Value = 1

dispatcher.executeDispatch(document, ".uno:GoDownSel", "", 0, args9())

rem ----------------------------------------------------------------------
dim args10(0) as new com.sun.star.beans.PropertyValue
args10(0).Name = "By"
args10(0).Value = 1

dispatcher.executeDispatch(document, ".uno:GoDownSel", "", 0, args10())

rem ----------------------------------------------------------------------
dim args11(0) as new com.sun.star.beans.PropertyValue
args11(0).Name = "By"
args11(0).Value = 1

dispatcher.executeDispatch(document, ".uno:GoDownSel", "", 0, args11())

rem ----------------------------------------------------------------------
dim args12(0) as new com.sun.star.beans.PropertyValue
args12(0).Name = "By"
args12(0).Value = 1

dispatcher.executeDispatch(document, ".uno:GoDownSel", "", 0, args12())

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

rem ----------------------------------------------------------------------
dim args14(0) as new com.sun.star.beans.PropertyValue
args14(0).Name = "Nr"
args14(0).Value = 2

dispatcher.executeDispatch(document, ".uno:JumpToTable", "", 0, args14())

rem ----------------------------------------------------------------------
dim args15(0) as new com.sun.star.beans.PropertyValue
args15(0).Name = "ToPoint"
args15(0).Value = "$A$1"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args15())

rem ----------------------------------------------------------------------
dim args16(5) as new com.sun.star.beans.PropertyValue
args16(0).Name = "Flags"
args16(0).Value = "SVD"
args16(1).Name = "FormulaCommand"
args16(1).Value = 0
args16(2).Name = "SkipEmptyCells"
args16(2).Value = false
args16(3).Name = "Transpose"
args16(3).Value = true
args16(4).Name = "AsLink"
args16(4).Value = false
args16(5).Name = "MoveMode"
args16(5).Value = 4

dispatcher.executeDispatch(document, ".uno:InsertContents", "", 0, args16())

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

rem ----------------------------------------------------------------------
dim args18(0) as new com.sun.star.beans.PropertyValue
args18(0).Name = "ToPoint"
args18(0).Value = "$A$1"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args18())

rem ----------------------------------------------------------------------
dim args19(0) as new com.sun.star.beans.PropertyValue
args19(0).Name = "Nr"
args19(0).Value = 1

dispatcher.executeDispatch(document, ".uno:JumpToTable", "", 0, args19())

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

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

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

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

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

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

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

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

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

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

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

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


end sub

Thank you

[Formatted for readability - robleyd]

#2

Your task description took five lines, your code (without the loop yet) took almost 200 lines. This means that the best thing you can do with your code is to throw it away and try to forget about it. I am not downvote your question because you are here for the first time, welcome.

#3

@josuesec,

Transpor works with a table, you can do it only once.

#4

What part of this code do you want to repeat 400 times?

Sub copyTransposedRange
Dim oSheets As Variant
Dim oSourceSheet As Variant, oTargetSheet As Variant
Dim oSorceRange As Variant, oTargetRange As Variant
	oSheets = ThisComponent.getSheets()
	oSourceSheet = oSheets.getByIndex(0)
	oTargetSheet = oSheets.getByIndex(1)
	
	oSorceRange = oSourceSheet.getCellRangeByName("N24:N35")
	
	oTargetSheet.getRows().insertByIndex(0, 2)
	
	oTargetRange = oTargetSheet.getCellRangeByPosition(0, 1, 11, 1)
	oTargetRange.setArrayFormula("=TRANSPOSE(" & oSorceRange.AbsoluteName & ")")
	oTargetRange.setDataArray(oTargetRange.getDataArray())
	
	oSorceRange.clearContents(-1)
End Sub
#5

Hi JohnSUN, thanks for your answer. I want to loop all the code, “select the cells, copy, paste transposed, and delete used rows” times x amount of times. The ‘delete rows’ part is for the new information I have below to be placed at the the deleted space. I didn’t write the code, instead I used the function at calc “record macro”. That might be the reason for the large number of lines in the code I posted. This is my first time using/recording a macro.

#6

I cannot say that I am beginning to understand. Column N contains a lot of data? 4800 values? Need to convert this column to a 12-column table?

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.