Hey there! I’m trying to figure out a Macro workflow for a while, but I’m hitting a wall… I also couldn’t find an answer the forum.
Here’s what I’m trying to do:
I have a variable amount of cells, that I want to copy, the amount of repeats is determined by the user. Let’s say I want to repeat it 10 times:
this is the loop-1 (repeated 10 times):
- I’m on cell-01 with text-01
- I’m copying text-01 to the clipboard and set variable-01 to the clipboard
- I’m increasing the variable number by the value 1
- I’m going to the next cell (cell-02)
now the loop repeats!
- I’m on cell-02 with text-02
- I’m copying text-02 to the clipboard and set variable-02 to the clipboard
- I’m increasing the variable number by the value 1
- I’m going to the next cell (cell-03)
.
.
.
- I’m going to cell-10 with text-10
- I’m copying text-10 to the clipboard and set variable-10 to the clipboard
- I’m increasing the variable number by the value 1
- I’m going to the next cell
Hope that makes sense so far. Now, how do automatically increase the variable numbers each round? I tried it with calculation, but that didn’t go that well…
The next step then is the other way around, where I basically paste the value of each variable in a new cell, starting with variable-01, ending with variable-10. That should be easy to solve, once I know a procedure for the first part.
Maybe someone has an idea to solve that?!
Thanks a ton!!!
Yes, you can do this.
Use the Prompt for User Input action to ask for the count, and the the For Each action with the Number Range collection to iterate through them.
The Set Variable to Text action allows the variable name to itself be a text token field, so you can set indexed variables to values.
To get the value out of the indexed variable is a bit tricky, you can to set a variable first to "%Variable%Cell 3%" and then use Filter Variable action to process the tokens in the variable a second time. To accomplish this, the % characters that you want preserved need to be doubled (so "%%Variable%%Cell 3%%"), and then you can use "%Variable%Loop%" instead of "3", so "%%Variable%%Cell %Variable%Loop%%%".
Note that there may well be better ways for doing this if you expect to use more than a handful of cells, such as writing the results to a file. But this would work ok for a dozen or so cells, after that I would start looking at a different kind of solution.
N Copy-Paste File.kmmacros (5.9 KB)
2 Likes
Peter! This is fantastic!! Thank you very much. I was not aware of the “For Each Item” Action. I will explore it more, I’m sure it will be very useful on other ends too.
Now, I tested the script a couple times and it works great - like you described - as long as there aren’t too many values. I also noticed, that maybe the string length could cause some glitches.
I’m facing something like 40-50 cells. I ran the script in a loop 20 times with 50 values and had approximately 10 glitches total of lost/mixed up values.
Just a quick update - I played with some “Pause for …” in between the steps and I did not had any glitches for the last 60 rounds (a 50 values). Works great!
What eventually happens is the keyboard event queue fills up and events get lost. So you need to ensure the system can keep up with the typing. It is not so much a problem with a couple cells, but when you have a lot, the application gets behind, the event queue fills up and bad things happen. As you discovered, a sufficient pause in the macro allows the system to fully complete each cell before progressing.
