[Solved] Recorded macro copy and paste

[robbyn]

The attached contains the recorded macro "tfr". It is supposed to copy one range to another location eg copy list to named cell "destination" where list is a range and destination is the first cell at the destination.

All I recorded was copy list then paste numbers only to destination (one cell).

It does not work as a macro. Manually the same steps do work.

What should I alter?

R

[robleyd]

Did you use the mouse to select anything whilst recording the macro?

See the HELP - F1; search macros;recording for information on the feature; including "Selections are recorded only if they are done by using the keyboard (cursor traveling), but not when the mouse is used."

[Lupp]

Code: Select all

sub recordedAndReworked()

dim document   as object
dim dispatcher as object
document   = ThisComponent.CurrentController.Frame REM Misleading!
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "ToPoint" REM Misleading!
args1(0).Value = "list"   REM Must be a defined named range!
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1()) REM Misleading! Actually selects a range.

dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array()) REM No args needed. Works with the selection.

dim args2(0) as new com.sun.star.beans.PropertyValue
args2(0).Name = "ToPoint" REM Misleading!
args2(0).Value = "destination" REM Must be a defined named range!
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args2()) REM Misleading! Actually selects a range.

REM Paste Special... has lots of options and setting!
dim args3(5) as new com.sun.star.beans.PropertyValue
args3(0).Name = "Flags"
args3(0).Value = "SVD"
args3(1).Name = "FormulaCommand"
args3(1).Value = 0
args3(2).Name = "SkipEmptyCells"
args3(2).Value = false
args3(3).Name = "Transpose"
args3(3).Value = false
args3(4).Name = "AsLink"
args3(4).Value = false
args3(5).Name = "MoveMode"
args3(5).Value = 4
dispatcher.executeDispatch(document, ".uno:InsertContents", "", 0, args3())
REM Works with the selected target cell, bot needs arguments to set options.

end sub

If you want to avoid the prompt for permission to overwrite, you may change/insert

Code: Select all

dim args3(6) as new com.sun.star.beans.PropertyValue
...
args3(6).Name = "Overwrite"
args3(6).Value = True

[MrProgrammer]

It is supposed to copy one range to another location eg copy list to named cell "destination" where list is a range and destination is the first cell at the destination.

My OpenOffice macro copies P7:P20 to F6:F19.
• Tools → Macros → Record
• Type list in Name Box and press Enter
• Menu: Edit → Copy
• Type destination in Name Box and press Enter
• Menu: Edit → Paste Special → Selection: Numbers → Options: None → Operations: None → Shift: Don't → OK
• Click Stop Recording button

If you need any additional assistance specify the exact sequence of steps you used to record your macro, providing every detail of keyboard and mouse actions.

It does not work as a macro.

This information is of no value because you do not say what the result was. We already know the result was not what you expected or you wouldn't be here. Never use "does not work" in your posts.

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the subject field. Select the green checkmark icon at the same time.

Using the Macro Recorder
[Tutorial] Favorite Recorded Calc Macros
[Tutorial] Ten concepts that every Calc user should know