Unanswered

Copy spreadsheet from one excel and paste into another excel

(2) Share

Report

Posted on by nok

Hello,

I have an Excel file that has several different spreadsheets, but I want to copy only the data from the "FormOP" spreadsheet and paste it into a new Excel file.
Is it possible to do this using MS Automate online?

Categories:

Building flows

I have the same question (0)

All responses (4)

Answers (0)

Suggested answer

Michael E. Gernaey 53,335 Super User 2025 Season 2 on at

Like (1)

Report

Hi,

Yes but you have many details missing.

1) Do you want this to run at a certain time of day, or week or is it manually triggered?

Its easy to do as long as the following

1) that the Spreadsheet that has the source data has an excel table wrapped around it AND that the Excel you want to paste too, also has a Table around the rows

you want to add too

2) then in your flow you would do

List rows from a Table (Excel Action). specifying the correct table

3) add an apply to each with the input being the output of step 2

4) for each Loop.

use Add row to a table (excel action)

where you populate the data for excel from the Apply to Each Loop

And done.

Its not hard but you have things that have to exist, such as the Tables I mentioned, knowing the location of the files etc.

1 people found this reply helpful.

Was this reply helpful? Yes No
shravanreddy13 30 on at

Like (1)

Report
Yes, you can automate the process of copying data from a specific sheet in an Excel file to a new Excel file using Power Automate (formerly Microsoft Flow). Here’s a step-by-step guide on how to set this up:

Step-by-Step Guide to Automate Copying Data Using Power Automate

Create a New Flow:

Click on "Create" from the left sidebar.

Choose "Automated cloud flow" or "Instant cloud flow" depending on whether you want to trigger it manually or automatically.

Define a Trigger:

For an Automated cloud flow, you’ll need to select a trigger like “When a file is created or modified” in OneDrive or SharePoint, depending on where your file is located.

For an Instant cloud flow, you can manually trigger the flow from the Power Automate interface.

Add Actions to Copy Data:

Get File Content:

Add a new step and select the "Get file content" action.

Choose your file location (OneDrive, SharePoint, or other).

Specify the path to your Excel file.

Read Data from Excel:

Add a "List rows present in a table" action from the "Excel Online (Business)" connector.

Select your file and specify the "FormOP" sheet from which you want to read the data. Ensure your data is formatted as a table in the Excel file for this to work.

Create New Excel File:

Add a "Create file" action.

Choose the location (OneDrive, SharePoint) where you want to save the new Excel file.

Specify the file name and path.

Add Rows to New Excel File:

Add a "Add a row" action to insert data into the new file.

Specify the file and table where you want to add the rows. You may need to format the new file similarly to the original.

Test Your Flow:

Save and test your flow to ensure it works as expected.

Check the new Excel file to verify that the data from the "FormOP" sheet has been copied correctly.

Detailed Steps for Reading and Writing Data

Get File Content

Action: Get file content

Location: Choose the location (e.g., OneDrive for Business, SharePoint)

File Identifier: Path to the Excel file containing the "FormOP" sheet.

Read Data from Excel

Action: List rows present in a table

File: Use the file identifier from the previous step

Table Name: Select the table corresponding to the "FormOP" sheet.

Create New Excel File

Action: Create file

Location: Choose the destination (e.g., OneDrive for Business)

File Name: Specify the name for the new Excel file

File Content: Use content or template as needed.

Add Rows to New Excel File

Action: Add a row into a table

File: Choose the new Excel file

Table Name: Specify the table in the new file

Row Data: Map the data from the "FormOP" sheet.

Notes

Ensure that both the source and destination Excel files have tables defined in them. Power Automate uses these tables to identify and manipulate data.

You may need to adjust the schema of your destination file to match the source file’s structure.

By following these steps, you can automate the process of copying data from a specific sheet in an Excel file to a new Excel file using Power Automate.

1 people found this reply helpful.

Was this reply helpful? Yes No
Suggested answer

takolota1 4,974 Moderator on at

Like (0)

Report

You can also use this template Azure Function to pull data from any Excel sheet without the need for table formatting...
https://community.powerplatform.com/galleries/gallery-posts/?postid=eea06380-8483-ef11-ac21-7c1e5201b42f

Was this reply helpful? Yes No
Suggested answer

RBoneck 415 on at

Like (0)

Report
For anyone else arriving late to this question, I am recommending you take a look at Office Scripts.

In Power Automate you can look at a file (get it from an email attachment or specify its location in OneDrive or SharePoint), and then use an Office Script to copy data from a specifically named tab/sheet in that file and use another Office Script to make a new tab in another file and paste that copied data in it.

The action is called "Run Script" and you can add it as many times as you need to run however many scripts it takes to achieve your desired results. Each script can only affect one workbook at a time, so you will need at least as many scripts as you have files to interact with.

Create scripts fairly easily in Excel:

-Open Excel
-Go to Automate tab
-New Script
-Write script (can use the Record Action option to help with this)
-Update name
-Save it

It's automatically saved in OneDrive under My files/Documents/Office Scripts

As an example, this is an Office Script which switches to a tab/sheet called FormOP if one exists in the current workbook.

function main(workbook: ExcelScript.Workbook) { /* This section switches the active view to a worksheet named "FormOP" */ // Check if the "FormOP" worksheet exists. let dataWorksheet = workbook.getWorksheet("FormOP"); if (dataWorksheet) { // Switch to the "FormOP" worksheet. dataWorksheet.activate(); } else { console.log(`No worksheet named "FormOP" in this workbook.`); } }

Was this reply helpful? Yes No