Merge multiple sheets into a new summary Excel sheet

About

Summary: Concatenate data from multiple sheets into one new Excel sheet. The initial data is not deleted.

Applications: OneDrive, Excel

Description: This examples explains how to create a workflow that gathers data from several Excel sheets and merges them into a new sheet, while preserving the initial data. This example presents Microsoft 365 activities such as Add Sheet, For Each Sheet in Workbook, Read Range, and Write Range.

Workflow

Tip: If you want to save time, use the Merge Multiple Sheets into a new Summary Excel Sheet template in Studio Web to build this workflow.

Open Studio Web and create a new project.
Add an Add Sheet activity to your workflow. At this step, you want to add a new sheet, called Summary, to a specific workbook. In the activity:
1. Set up your Microsoft OneDrive & SharePoint connection.
2. In the Workbook field, browse and choose the workbook where you wish to create a new sheet.
3. Set the Sheet name to Summary. Choose any name that suits your usecase best.
Next, add a For Each Sheet in Workbook activity, to iterate on the sheets you wish to merge. In the activity:
1. Select the same workbook.
2. In the Body of the activity, add an If activity. Configure the activity as follows:
  1. Set the Condition for the iteration. Click the Plus icon to select Use variable.
  2. Set the condition as: CurrentItemIndex=0.
Next, add an activity for the Then branch of If. This is the activity to execute in case the condition is true. Add a Read Range activity. Its purpose is to use the first sheet headers in the Summary range and copy its data. Configure it as follows:
1. Select the workbook.
2. For the Range field, click the Plus icon to select Use variable, then select CurrentItem.
3. Set the Has headers field to True.
4. Set the What to read field to Values.
Right after the Read Range activity (still under the Then branch), add a Write Range activity and configure it as follows:
1. Select the workbook.
2. For the Range field, click the Plus icon to select Use variable, then select the output variable of the Add sheet activity.
3. Set the Include headers field to True.
4. Set the How to write field to Overwrite.
5. For the What to write field, select the output variable of the Read Range activity.
Add a Log Message activity to record that the first sheet (CurrentItem) has been added to the Summary sheet (New sheet name). This step is optional.
1. Configure the Message field as follows:
Next, set the Else branch of If, the activity to execute in case the condition isn't true.
1. Add another If activity to create a loop. For this If activity, you must set a condition that determines if the next sheets to be read are different than the Summary sheet. At this step, we copy the other sheets, excluding the headers.
  1. Configure the Condition Builder as follows: CurrentItem variable not equals NewSheetName (output variable of Add Sheet). In the Expression Editor, the condition would look like this: CurrentItem <> _out_AddSheetConnections_1__NewSheetName.
Set the Then branch. Add a Read Range activity to read through the sheets in the workbook:
1. Select the workbook.
2. For the Range field, select Use variable and choose the CurrentItem variable.
3. Set the What to read field to Values.
Next, add a Write Range activity, to write the data read into the Summary sheet.
1. Select the workbook.
2. For the Range field, select the New sheet name variable of the initial Add Sheet activity.
3. Set the How to write field to Append. To learn more about appending, see the activity's documentation.
4. Set Include headers to False.
5. For the What to write field, select the output variable of the Read Range activity.
Next, add a Log Message activity, still under the Then branch, to record that all the sheets have been appended into the Summary sheet. This step is optional.
End the workflow with another Log Message activity, to record how many sheets were processed aned merged into the Summary sheet. This step is optional.
You're done and ready to run your project!