[Solved] Creating Simple Copy Paste Macro

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
TomMassey423
Posts: 7
Joined: Thu Mar 25, 2021 10:02 pm

[Solved] Creating Simple Copy Paste Macro

Post by TomMassey423 »

I need a macro that copies a row of figures, copies and moves them over one column, then returns to the orginal cell and deletes the data so new data can be entered.

I have attempted using the Macro recorder, but when I run the macro it moves over a column for each instance it has been run. I cannot find anything in the code that would cause this.

I am setting up a spreadsheet to record golf scores and to calculate averages for the golf group. For each round played, a new score needs to be entered for each golfer and all the old scores moved over one space. I need to be able to move the cursor to the next golfer and repeat the process. We have 15 to 25 golfers 6 days a week to record scores.

I also need a keyboard shortcut for this macro (Ctrl-X) to make calling the macro easy as scores are entered.

Can anyone help?
Last edited by Hagar Delest on Wed Mar 31, 2021 7:18 am, edited 1 time in total.
Reason: tagged solved.
Thomas Massey; Open Office 4.1.7; Windows 10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Creating Simple Copy Paste Macro

Post by Villeroy »

If it is a simple copy&paste macro, you can do this by hand very easily. Either you program or you don't program. For simple copy&paste operations I would never take the effort to write a macro (although I could but it really does not pay off).
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
TomMassey423
Posts: 7
Joined: Thu Mar 25, 2021 10:02 pm

Re: Creating Simple Copy Paste Macro

Post by TomMassey423 »

I am creating the spreadsheet for someone who is very uncomfortable with using spreadsheets. I need the macro to simplify the data entry for this person.
Thomas Massey; Open Office 4.1.7; Windows 10
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Creating Simple Copy Paste Macro

Post by Lupp »

What would "somebody very uncomfortable with spreadsheets" do if the "simple copy/paste macro" is called by accident at the wrong time, or produces an unexpected result? The crux of "simple macros" is that they often only are simple because they don't catch expectable errors and don't take precautions to correct them.

If you can write the macro yourself, you may be able to think of some special cases. If you ask somebody eles to do it, you need to describe the task to every detail. You need to completely and unambiguously specify the piece of automation you want to get.

Is the "original cell" always the same cell or needs the macro to memorize it? Does always the same range of cells need to be moved, will it grow, must it be selectable? Why do you want to move a part of a row? (It's unusual when entering subsequent data.) Why don't you insert first a new column?

Did you consider if a recorded macro might solve your problem?
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
TomMassey423
Posts: 7
Joined: Thu Mar 25, 2021 10:02 pm

Re: Creating Simple Copy Paste Macro

Post by TomMassey423 »

The spreadsheet has the golfer's name in column B, then scores begin in column D. A single golfer can have 1 upto 10 scores.
The process I am trying to automate is place the cursor on the row with the golfers name, put the cursor in column D, then execute the macro to move all the existing scores over one column and clear the contents in column D so the new score can be entered. Then go to a new golfer and repeat the process. The order of entry is random as the golfer's new scores are not sorted in any order.

I have tried to train the person entering the scores, but he says the process is too complicated. Currently he simply enters the scores in the next avaiable column. He is currently working in column JK for some golfers, and column JA for others, depending on how many rounds the golfer has played.
Thomas Massey; Open Office 4.1.7; Windows 10
User avatar
Zizi64
Volunteer
Posts: 11352
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Creating Simple Copy Paste Macro

Post by Zizi64 »

I have tried to train the person entering the scores, but he says the process is too complicated.
Give them a paper and a pencil.
:D :) ;) :(
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Creating Simple Copy Paste Macro

Post by Villeroy »

TomMassey423 wrote:The spreadsheet has the golfer's name in column B, then scores begin in column D. A single golfer can have 1 upto 10 scores.
The process I am trying to automate is place the cursor on the row with the golfers name, put the cursor in column D, then execute the macro to move all the existing scores over one column and clear the contents in column D so the new score can be entered. Then go to a new golfer and repeat the process. The order of entry is random as the golfer's new scores are not sorted in any order.

I have tried to train the person entering the scores, but he says the process is too complicated. Currently he simply enters the scores in the next avaiable column. He is currently working in column JK for some golfers, and column JA for others, depending on how many rounds the golfer has played.
This happens when you choose a cross-table layout where each value needs to be entered at the crossing of a distinct row and a distinct column. This layout is difficult to maintain and it is difficult to analyse when you want to get information from data, info about rankings and other statistics. Data input into cross-tables is a mistake -- always with no exception.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
MrProgrammer
Moderator
Posts: 4894
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Creating Simple Copy Paste Macro

Post by MrProgrammer »

TomMassey423 wrote:The process I am trying to automate is place the cursor on the row with the golfers name, put the cursor in column D, then execute the macro to move all the existing scores over one column and clear the contents in column D so the new score can be entered. Then go to a new golfer and repeat the process.
Record a macro. Based on my understanding of your layout and your objective, I suggest recording this one action (with the cell in column D selected before recording begins):
• Insert → Cells → Shift cells right → OK
Then save your macro.

For any additional assistance attach a document demonstrating the situation (remove confidential information then use Post Reply, not Quick Reply, and don't attach a picture instead of the document itself). Explain precisely:
• The pre-conditions (what range is selected and what cell is the active one)
• What you want the macro to do (move cells {specify} to cells {specify}, other actions?)
• The post-conditions (what range is selected and what cell is the active one)
TomMassey423 wrote:I also need a keyboard shortcut for this macro (Ctrl-X) to make calling the macro easy as scores are entered.
Do not attempt to use any of Ctrl-A/C/V/X because they are reserved shortcuts. Ctrl-B/F/I/N/O/P/S/U/W/Z would be bad choices too. You can use Ctrl-Shift-X, and more generally Ctrl-Shift-{anything}.
[Tutorial] Binding a macro: Shortcut key, menu or toolbar
TomMassey423 wrote:I have tried to train the person entering the scores, but he says the process is too complicated.
Be sure to train the person about Edit → Undo (Ctrl-Z) if they make a mistake.
TomMassey423 wrote:… scores begin in column D. A single golfer can have 1 upto 10 scores. He is currently working in column JK for some golfers, and column JA for others …
Your explanation seems strange. JA is column 261. JK is 271!

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.

[Tutorial] Ten concepts that every Calc user should know
Mr. Programmer
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel.   The locale for any menus or Calc formulas in my posts is English (USA).
TomMassey423
Posts: 7
Joined: Thu Mar 25, 2021 10:02 pm

Re: Creating Simple Copy Paste Macro

Post by TomMassey423 »

In the attached file, sheet 1 is the original file given to me by the person responsible for maintaining the data for the golf group.
The 2nd sheet, labeled "copy" is where I have reformatted the data and attempted to create the macro "CopyPaste". I have not attempted to create a keyboard short cut, but I do need one.

I recorded the macro starting in Cell E3 and named it "CopyPaste", I then placed the cursor in E6 and ran the macro "CopyPaste".

I then put the cursor in E9 and ran the macro CopyPaste again. As you can see, the macro moves one column over each time it is run, even though the cursor begins in Column E

I cannot use the Insert Cells function because it changes the formula used to calculate the quotas listed in the row below the scores.

I did not expect this to be this hard. This is my first attempt at creating a macro, and what I thought would be a very simple, straight forward macro has stumped me.
Attachments
SandBaggersnew Open Ofc 2.ods
(88.33 KiB) Downloaded 244 times
Thomas Massey; Open Office 4.1.7; Windows 10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Creating Simple Copy Paste Macro

Post by Villeroy »

You can not write any macro because you can not program.
Your spreadsheet layout is inadequate which makes everything extremely complex. Nobody needs any macros with a proper table layout.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
MrProgrammer
Moderator
Posts: 4894
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Creating Simple Copy Paste Macro

Post by MrProgrammer »

TomMassey423 wrote:… and attempted to create the macro "CopyPaste".
Your macro is not present in the attachment, probably because you saved the macro in My Macros instead of your spreadsheet. I cannot view, and you did not specify, what steps you recorded. I could guess that it is the step I suggested, but the behavior of your macro makes me think that it is not. If you're not going to put the recorded macro in your spreadsheet, it will not be available when you give the spreadsheet to someone else.
NoMacro.png
TomMassey423 wrote:As you can see, the macro moves one column over each time it is run, even though the cursor begins in Column E
This does not occur for me with the macro action which I suggested. I do not know what you recorded. I can see that the macro you recorded shoves the values into O (row 3), then O and P (row 6), then O and P and Q (row 9). Is this what you want to happen? Or should the macro keep ten values only? I don't know, and you don't say.
TomMassey423 wrote:I cannot use the Insert Cells function because it changes the formula used to calculate the quotas listed in the row below the scores.
You failed to describe the pre-conditions and post-conditions. That is why I asked for them. The pre-conditions should have specified that you have important formulas related to the cells being moved. You did not mention this situation in your first three posts. The post-conditions should have specified how these formulas should behave after the macro runs. You have not specified what the formulas should calculate. I can see what they are, but they are not consistent and are thus confusing.
=ROUND(SUM(E12:N12)/COUNT(E12:N12);1)
=ROUND(SUM(F12:N12)/COUNT(F12:N12);1)
=ROUND(SUM(G12:N12)/COUNT(G12:N12);1)
=ROUND(SUM(H12:N12)/COUNT(H12:N12);1)
=ROUND(SUM(I12:O12)/COUNT(I12:O12);1)
=ROUND(SUM(J12:P12)/COUNT(J12:P12);1)
=ROUND(SUM(K12:Q12)/COUNT(K12:Q12);1)
=ROUND(SUM(L12:R12)/COUNT(L12:R12);1)
=ROUND(SUM(M12:S12)/COUNT(M12:S12);1)
=ROUND(SUM(N12:T12)/COUNT(N12:T12);1)
In the first four, the range ends at column N. In the others the range extends to the right. I can't think of a good reason to use SUM(E12:N12)/COUNT(E12:N12) instead of AVERAGE(E12:N12). I have no idea what you want the formulas to do. I can't help you when you fail to provide the requested information. Perhaps we can adjust the formulas so they behave as you want, but you need to say what the calculation should be.
TomMassey423 wrote:… what I thought would be a very simple, straight forward macro has stumped me.
What I thought would be simple, answering a question about using a macro to shift values to the right, has stumped me.
Mr. Programmer
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel.   The locale for any menus or Calc formulas in my posts is English (USA).
TomMassey423
Posts: 7
Joined: Thu Mar 25, 2021 10:02 pm

Re: Creating Simple Copy Paste Macro

Post by TomMassey423 »

I have recorded a new macro CopyPaste2 and saved it under Sandbaggersnew Open Ofc 2; Standard; Module 1

To record, I put the cursor in E3, then Start recording: Shift End of Row, Copy, Go Left (9 times to F3), Paste (Paste over current data), Go Left (to Column E); Delete (Delete Current Data); End Recording. I have copied the generated documentation and included the file as CopyPaste2 Documentation.

I ran the macro by placing the cursor in E6 and going to Tools to call the macro CopyPaste2, and it moved the results over an additional column.

I then ran the macro by placing the cursor in E9, and the results were again moved one column, which is different from my original issue. I used the same process to record both macros, so I do not understand the variance

As the values are moved to Col O and beyond, they are ignored in calculating the current quota in Col E. These values should be deleted for clarity, but that is not what the current person does when maintaining his files.

Only the formulas in Col E are needed. A new golfer will slowly accumulate scores until they reach 10, older scores are then ignored to create a current quota calculation. I was not sure the Average function would accurately calculate the average as the new scores were added, so the Count function was used. The other formulas are only there to duplicate the spreadsheet the person maintaining the file was accustomed to seeing. The formulas may not be consistent, but they perform the function of maintaining consistency in view from the old file to the new file.

Thank you for taking the time to help. As I stated before, I thought this was simple - record macro, exedute macro - not as simple as I thought.
Attachments
CopyPaste Original Documentation.odt
(12.89 KiB) Downloaded 241 times
CopyPaste2 Documentation.odt
(12.87 KiB) Downloaded 218 times
SandBaggersnew Open Ofc 2.ods
(90.8 KiB) Downloaded 246 times
Thomas Massey; Open Office 4.1.7; Windows 10
User avatar
Zizi64
Volunteer
Posts: 11352
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Creating Simple Copy Paste Macro

Post by Zizi64 »

I thought this was simple - record macro, exedute macro - not as simple as I thought.
You MUST WRITE your macros (instead recording them) if yo want work efficiently with the macros. You need studí the API functions. API: application Programming Interface.

And you can assign the macros to your own Menu item, to a free Hotkey, to a Toolbar icon, to a Form control element (Button) for easier launching of the macro.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Creating Simple Copy Paste Macro

Post by Villeroy »

Collect data in a database.
Open the attached document. It is a quick draft of a database.
Open the form therein. The yellow drop-down box with the player names has the input focus.
Type the first letters of a player name, hit Tab when you got the wanted player.
Enter a number into the second box.
Hit Enter to save the 2 values and proceed to the next new record.
Hit Tab and enter the next player name ...
The [OK] button is triggered by the Enter key, the [Cancel] button lets you abort any unsaved input in the yellow boxes.
-------------
The entered values can be monitored, edited and deleted in the table grid below the entry area after hitting the [Refresh] button. Latest entries appear above older entries.

The main database window has also a "Reports" section with one report. A report is a printable representation of database data. This one shows min, max, sum, count and average of all values for each player.
Attachments
Scores.odb
(24.6 KiB) Downloaded 226 times
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
TomMassey423
Posts: 7
Joined: Thu Mar 25, 2021 10:02 pm

Re: Creating Simple Copy Paste Macro

Post by TomMassey423 »

Thanks for your reply, but this is a simple spreadsheet, and needs to remain a simple spreadsheet and not converted to a database. The person maintaining the file will not attempt the change to a database format. These are all retiree's who play golf together and like to keep scores to handicap their games. All volunteer, untrained, uncompensated help is all that is available.
Thomas Massey; Open Office 4.1.7; Windows 10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Creating Simple Copy Paste Macro

Post by Villeroy »

No, it is not a simple spreadsheet. It is too difficult to use. If your users can't handle a form with 2 input boxes then they can not use any computer program at all.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
MrProgrammer
Moderator
Posts: 4894
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Creating Simple Copy Paste Macro

Post by MrProgrammer »

TomMassey423 wrote:I used the same process to record both macros, so I do not understand the variance
You did not use the same process to record both macros. Look at your two Documentation.odt files. The original has a copy step (.uno:Copy), the new one does not. Isn't this obvious? Even Tools → Word Count shows that the documents are not the same.
TomMassey423 wrote: ran the macro by placing the cursor in E6 and going to Tools to call the macro CopyPaste2, and it moved the results over an additional column.
CopyPaste2 cannot work because you failed to copy the cells to the clipboard in your macro.
TomMassey423 wrote:I then ran the macro by placing the cursor in E9, and the results were again moved one column, which is different from my original issue.
Your post does not say whether or not your problem continues or whether you are just making an observation. Based on the omission of a copy step, I will guess your problem is not solved. I find that I must do a lot of guessing about what you want.

But now I have a bit more information about your task, since you've attached a spreadsheet which shows that formulas are involved and then said the only formulas which are important are the ones in column E. There are many ways to record your macro. I would use these steps, assuming that the data is in columns E through N, and column D is empty, and column O is empty:
• Select a data cell in column E, say E3
Start recording
• Go right to end of data (⌘→ on a Mac, Ctrl+→ on other platforms, active cell is now in column N)
• Go left (←, active cell in column M)
Select left to start of data (⇧⌘← on a Mac, ⇧Ctrl+← on other platforms, active cell in column E with nine cells in E to M highlighted)
• Edit » Copy (active cell in column E with nine cells highlighted, nine cells copied to clipboard)
• Go right (→, active cell in column F)
• Edit » Paste (active cell in column F with nine cells in F to N highlighted, nine cells copied from clipboard)
• Go left (←, active cell in column E)
• Edit » Delete Contents » Delete All » OK (active cell in column E is cleared)
Stop recording (active cell back in column E)

You must ensure that the active cell is in column E when you run the macro! You must use Copy in the macro, not Cut. Using Copy, the formulas in the following row are not affected. The value of the formula in E4 will change, of course, since E3 is now empty. Recording a macro for this task only requires that you know how to use Calc's standard user interface and that you know how formulas work. If you don't have that knowledge, attempting to record anything is pointless. If the columns to the left and right of the data (D and O) are not empty you will not be able to use "Go right to end of data" and "Select left to start of data", but you can use simple "Right" and "Select Left" the appropriate number of times. Learn about these commands by reading the documentation for Calc's user interface.
TomMassey423 wrote:I was not sure the Average function would accurately calculate the average as the new scores were added, so the Count function was used.
AVERAGE ignores empty cells. It will calculate the same result as SUM/COUNT. E4 can use =AVERAGE(E3:N3). You don't need ROUND since the cell's format (Format ← Cells → Numbers → Format Code → 0.0) rounds the values for display.
TomMassey423 wrote:The formulas may not be consistent, but they perform the function of maintaining consistency in view from the old file to the new file.
So it's better to have incorrect formulas than no formulas? LOL

If you still can't get a recorded macro to do what you want, I suggest that you use paper and pencil for the task. I've spent at least an hour of my time helping you (three posts here). You can easily find templates designed for golf leagues.

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.
Mr. Programmer
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel.   The locale for any menus or Calc formulas in my posts is English (USA).
TomMassey423
Posts: 7
Joined: Thu Mar 25, 2021 10:02 pm

Re: Creating Simple Copy Paste Macro

Post by TomMassey423 »

Thank you very much for taking the time to help me. I was able to get the macro to do what I need. I will now try to assign a keyboard shortcut to make it easier to do.
Thomas Massey; Open Office 4.1.7; Windows 10
Post Reply