Regarding Copy & Paste from 1 Excel workbook to another workbook ( in separate worksheets)

(0) Share

Report

Posted on by SA_VOH

231

Hi Community,

I shall explain my problem in detail:-

1. I have 2 workbooks named "A" and "B", which are enclosed.

2. In workbook "A", i have date-wise data, i.e 01-Nov-22, 02-Nov-22 and so on and it shall be limited to calendar month of Nov-22 and shall be updated on a daily basis (right now showing till 27-Nov-22 but it shall expand to 30-Nov-22).

3. For each date, i have data from row No. 2 to row no. 97.

4. In workbook "B", i have created date-wise sheets named 1,2,3 and so on till 30 (representing 30 dates in November) alongwith a Summary sheet which is blank. These sheets were prepared well in advance and are not created on a daily basis.

5. My requirement is that i would like to -

Copy cells from 01-Nov-22 column - i.e. from A2 to A97 (from workbook "A") and paste it in cells B3 to B98 in "1" worksheet of workbook "B"

Copy cells from 02-Nov-22 column - i.e. from B2 to B97 (from workbook "A") and paste it in cells B3 to B98 in "2" worksheet of workbook "B".

and so till the date for which date is present in workbook "A", which means that if data is available till 27-Nov-22, then workbook "B" will be updated till worksheet "27" only and the PAD loop will exit after that.

Everytime, the cells will be pasted in B3 to B98 in workbook "B" only.

Please help with Power Automate Desktop.

B.xlsx

A.xlsx

Categories:

Power Automate Desktop

I have the same question (0)

All responses (4)

Answers (1)

Verified answer

MichaelAnnis 5,727 Moderator on at

Like (0)

Report
Copy link

Link copied!

Launch Excel, worksheet A to %ExcelA%
get first free column on row 2 to %FirstFreeColumn%

Set variable %LastColumn% to %FirstFreeColumn -1%

read from Excel single value at row 1 and %LastColumn% to %ExcelValue%
’convert to date if needed

Set variable %Days% to %ExcelValue.Day%

Launch Excel worksheet b to %ExcelB%
Loop 1 to %Days% increment of 1
Copy cells from %ExcelA% from row 2 column %LoopIndex% to row 98 column %LoopIndex%

Activate worksheet %LoopIndex% in %ExcelB%
Select cell B3

Paste

End(Loop)

if formatting copy and paste becomes an issue, it is because they are in two separate excel instances. In this case, instead of Launch worksheet B, you will want to use the UI or send keys to File-> Open Worksheet B from worksheet A’s window, so it exists in the same Excel instance and then attach to it as %ExcelB% after it is already open.

On another note, if you just wanted to always do all 30 or 31 days, it wouldn’t hurt. You could do away with the loopmax logic, trouble with dates, etc, and it’s just doing a few extra iterations of the loop to copy blank cells when the month isn’t finished.

Was this reply helpful? Yes No
SA_VOH 231 on at

Like (0)

Report
Copy link

Link copied!

Hi MichaelAnnis Sir,
Thanks for the solution.
I will check the solution and revert back.

Was this reply helpful? Yes No
SA_VOH 231 on at

Like (0)

Report
Copy link

Link copied!

Hi MichaelAnnis Sir,
Apologies for delay in checking the solution on my side. However, the solution is found to be ok but i have done one slight modification- i.e. "Instead of Copy and Paste Cells" inside the Loop, i have opted for "Read from and write to in Excel" because the former is leading to formatting getting pasted in Excel "B", which i want to avoid.

Please let me know that whether there is any difference in "Copy/Paste" and "Read/Write" for Excel in PAD or whether both them perform the same function except formatting.
Regards,

Was this reply helpful? Yes No
MichaelAnnis 5,727 Moderator on at

Like (0)

Report
Copy link

Link copied!

Right. Copy and paste brings formatting. If read/write is working for you, then great. Another option would be copy, paste values which is more difficult. As long as it’s working, that is always the goal.

What is the difference?

copy and paste will keep formatting and formulas (same as pressing control c and control c).

read and write is like a human reading excel in one workbook and typing it into another.

good luck. Glad u got it working.

Was this reply helpful? Yes No