Need a help in Copy and Paste issue in Power automate desktop in Excel

[Ahmedmuz]

Hi Everyone,

I need to copy data from a pivot table and paste it into an Excel sheet where permanent headers are present. The challenge is that the pivot table may not always have the same headers. For example, in the snapshot below, the table has four headers: Apple, Ball, Cat, and Dog. However, sometimes the pivot table may not include the "Cat" header. In such cases, I need to copy and paste the data while leaving the "Cat" column empty. How can I achieve this in powerautomate desktop flow ?

[Nived_Nambiar]

Hi @Ahmedmuz 

It is possible.

Sharing a demo flow with steps to get an idea on how to design such flows 🙂

So i have created a excel file , it has three sheets : Sheet1 , Sheet2, Sheet3 (Sheet3 we won't discuss).

Sheet2 has Pivot table (datasource is from Sheet3)

Now go to flow 

Step1: Launch the excel file by passing the file path 

Step2: Make sheet2 (which has pivot table) as active 

Step3: Get the first free row/column from active worksheet (i.e Sheet2)  and for easy reference, i have named both variables (first free row and first free column) as PivotFreeRow and PivotFreeColumn respectively.

Step4: Read the data from active worksheet by passing data from previous step and store in a datatable variable, let's say PivotData

Step5: Now set active worksheet as Sheet1 (which is the table we are writing the data from pivot table)

Step 6:Read the data from active worksheet (i.e Sheet1) like below

Note: here i am reading data from A1 to D1 , only for the headers data, in my case my headers in Sheet1 are upto D1, so provided like that you can change the D to required column letter to which your column is present in the worksheet where you need to write the data.

Step7: Create a new datatable variable named %dt1Final% with value like below

this will create a dattable with headers only of that of Sheet1.

Step 8: Create a new list let's say %rowitem% (this would be for temporary data storage)

Step 9: iterate through each row of PivotData datatable using for each action

Let's discuss what happen inside the loop in sub steps 

Step 9.1: Clear the rowitem list

Step 9.2: Now loop through each col in %ExcelData.Columns% like below

 Step 9.2.1: check %PivotData.Columns% contains %col% value in it, if 

  value Present: then add %row[col]% to %rowitem% list

 value not present: then add %''% to %rowitem% list 

Step 9.3 : append the rowitem to dtFinal like below

Step 10: use write to excel to write the dtFinal to excel file like below 

Step 11: close the excel

So overall flow looks like this:

Hope this design helps !

Thanks & Regards,

Nived N 🚀

LinkedIn: Nived N's LinkedIn
YouTube: Nived N's YouTube Channel

🔍 Found my answer helpful? Please consider marking it as the solution!
Your appreciation keeps me motivated. Thank you! 🙌