Power Automate pasting cells in middle of excel sheet

(0) Share

Report

Posted on by remabsh

Hi,

I'm a newbie to power automate and have been stuck trying to figure this out for the past 3 days.

I'm trying to create a flow in which power automate will facilitate the copying and pasting of data from Excel Workbook 1 to Excel Workbook 2. The flow is working, but it keeps pasting the data in the middle of the worksheet whereas I want it to paste on the first free row under the first column.

I've tried everything, including Go to special > visible cells only. Each time I run the flow, the data gets pasted on Column A, row 893 even though row 2 is free. I don't want to specify to paste on row 2, because I want new rows to be added in Excel 2 when a new Excel is added to onedrive. I want it to paste on the first available row.

This is my flow. You'll notice disable steps because I experimented between read/write vs copy/paste to see if this would help but it gives me the same results.

Please help.

Thanks!

Categories:

Power Automate Desktop

I have the same question (0)

All responses (4)

Answers (2)

Sort by

Verified answer

lbendlin 8,716 Super User 2026 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

Remember that Power Automate doesn't work with sheets, it works with named ranges and tables AND it requires a primary key identifier for each row in that declared table for delete/update operations. If your Excel sheet has empty rows then Power Automate panics and writes (inserts) to the end of the range.

Was this reply helpful? Yes No
momlo 1,527 Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @remabsh
Open your workbook and hit ctr+end to go to the last used cell. That's where PAD will paste data when you get the "last row/column."
In other words - most likely, you need to clean/fix your workbook 🙂

https://support.microsoft.com/en-us/office/locate-and-reset-the-last-cell-on-a-worksheet-c9e468a8-0fc3-4f69-8038-b3c1d86e99e9

Was this reply helpful? Yes No
remabsh 9 on at

Like
a
(0)

Report
Copy link

Link copied!

Thank you guys! My first Excel worksheet did have empty rows in between the data, so I deleted them.
Excel one is pulled from an on-premises database query and saved in one drive. If these reports are pulled with empty rows, do I need to create a flow before the flow above in which power automate deletes empty rows?

is that even possible?

Was this reply helpful? Yes No
Verified answer

momlo 1,527 Super User 2024 Season 1 on at

Like
a
(2)

Report
Copy link

Link copied!

Yes, you need to clean the workbook if you want to use the get first row/column or get data via SQL (from an Excel file).

I almost always use SQL query to pull data from excel this way, and you can use WHERE statement to pull data only if fields have value

https://learn.microsoft.com/en-us/power-automate/desktop-flows/how-to/sql-queries-excel

Was this reply helpful? Yes No