How to get dynamically the last excel worksheet, copy the data and paste in a new worksheet?

(0) Share

Report

Posted on by EBMRay

1,712

Hello everyone,

I started learning Power Automate Desktop and I am trying to achieve the below scenario:

1. Launch an excel file.
2. Get the last tab or go to the last tab and copy all information from the previous tab.
3. Create a new tab and paste the information in.

So basically an excel sheet might have 20 worksheets and during the flow process, it should always find the last excel worksheet -> copy the data from the previous worksheet (worksheet no.19) -> create a new worksheet and paste the information that is coming from the worksheet no.19.

It is not always 20 worksheets so the flow should be dynamic and always find the last sheet.

What I tried is the below:
PDA screenshot.png
COpy to sheet 5.png

However, I am still struggling to get the last worksheet and the one before the last worksheet - 1 to copy the data and paste in a new worksheet.

Could please provide some screenshots or an example on how to achieve the above?
Any help is highly appreciated.

Best regards,
Julien

Categories:

Power Automate Desktop

All responses (4)

Answers (1)

Sort by

Verified answer

VJR 7,635 on at

Like
a
(2)

Report
Copy link

Link copied!
Hi @Julien2

Lets assume "Get all Excel Worksheets" returns the below Datatable.

Then no need to use a loop. Directly use the count of the datatable with minus 1 and minus 2 from the count.

The Datatable starts from 0, but the count starts from 1. Therefore need to use as below.

The last sheet name is: %SheetNames[SheetNames.Count - 1]% The second last sheet name is: %SheetNames[SheetNames.Count - 2]%
EBMRay 1,712 on at

Like
a
(0)

Report
Copy link

Link copied!

Hello @VJR ,
Thank you for providing the above example and screenshots.

As the next step since now we have the "second last sheet name", how can I copy this data from this sheet and paste it into a new worksheet?

I found the action to create a new worksheet but I am struggling with the action that should be used to read only the second last sheet name.

Could you please also provide an example of that scenario?
Awaiting your response.
Best regards,
Julien
VJR 7,635 on at

Like
a
(1)

Report
Copy link

Link copied!

@Julien2

you can first activate the worksheet you want using "Set active Excel worksheet" then copy the cells and then again activate the other worksheet and then paste. Hope this helps.
EBMRay 1,712 on at

Like
a
(1)

Report
Copy link

Link copied!

Hello @VJR ,

Thank you so much for your reply. It worked perfectly.
Have a nice week ahead!
Best regards,
Julien