PAD excel auto copy to bottom

[Anonymous]

Hi all,

I am trying to copy below green highlighted values (each columns) to the bottom end, where the values in Column B ends.

But, please be in mind that the values in column B changes every time so it would be great if copying down other columns can be changed accordingly. It would be greatly appreciated if someone could help me out.

[MichaelAnnis · Accepted solution]

Launch Excel

get first free row on column (B) to %FFRB%
Select cell range AA2

Send keys {Control}(c) ‘send as hardware keys=True

Select cell range AA2:AA and %FFRB - 1%
Send keys {Control}(v) ‘send as hardware keys=True

repeat same copy and paste steps for:

AF:AH

AJ:AK

should be done. 

Good luck!

[ThomasFonn · Accepted solution]

Hi @Anonymous 

1. Start off by making a loop or create a subflow to make everything resuable.

2. Copy or Cut the values from the row you wanted to move

3. Use the action called "Get first free row on column from Excel worksheet" which you point to column B

It returns the row number below "A22" as a variable called "FirstFreeRow" as default.
4. Use the action "Paste cells to Excel worksheet" and in row* property; type %FirstFreeRow -1%
This will target the row with the text "A22"

Hope this helps

[Anonymous]

Hi Thomas,

Thanks for the suggestion.

Could you advise me how I can use the loop in more detail?

I am not quite familiar with the loop so it would be helpful which parameter I should insert for this case.

BR,

[ThomasFonn]

To use a loop or a subflow depends a bit on how/why your files change and how you want a finished file to look.

Is the file in your original post something that is generated from a template, and there are many files that are similar?

If so, you could first use the Get files in folder-action and use the file filter with *.xlsx to only get excel-files. (This can be somewhat slow if your folder contains very many excel files)

Then you use a For Each Loop where you:

• Open the Excel instance,
• Do the copy/paste-actions,
• Save and exit

and then it will repeat these actions until you've gone through all the files.

Here's what this looks like

This may not be your scenario, but with loops you can do pretty much anything that needs to be repeated. Be that within a single file, or with multiple files as loops can be nested

If you want more help, feel free to DM me and I can try to help you on my spare time

Best of luck!

[Anonymous]

Hi Thomas,

Thanks for the detailed explanation.

It really helped my understanding!

Best regards