Split excel worksheet into multiple sheets

[MB35]

Hi,

I am trying to find a way to split a 'Master' worksheet into multiple worksheets, initially within the same workbook. 

For instance, if I have 10 departments listed in the Master sheet, I will end with 10 individual worksheets, one for each department with the relevant data.

I've found various videos on VBA which I'm muddling through but is there a way to do this in Power Automate? Any help is hugely appreciated. Thanks

[trice602]

Hi @MB35 ,

Here's the easiest way to do this, please consider and mark as a solution to help others find this framework too!

Trigger = instant

List rows present in a table

Switch/case

If case = department1

List rows present in a table 2, filter is department eq 'department1'

Apply to each

Add a row in a table (the new empty table for department1)

If case = department2

List rows present in a table 3, filter is department eq 'department2'

Apply to each

Add a row in a table (the new empty table for department2)

...

If case = department10

List rows present in a table 11, filter is department eq 'department10'

Apply to each

Add a row in a table (the new empty table for department10)

I've done this a handful of times and works really well!  The key here is you have to list rows present in a table first to be able to use your switch/case action.  Then you list rows present in a table again for each case with the rows filtered to that individual department, then loop through and add rows.

[grantjenkins]

Just a few questions to clarify.

1. What is your trigger?
2. Does your Excel file have a Table defined that contains your table data (Table1 for instance)?
3. Will you always have the same departments, or could it vary by name and how many?
4. How many rows of data (approx.) would there be in your master?

[MB35]

Hi @grantjenkins, 

The data can be set as a table or a range.

Departments will vary depending on the results of a report refresh, there could be upto 20 or so different names however not all will return data each time the report is run.

The source data has over 1000 rows however currently I run a manual filter to pull out the required rows which brings back upto 50. 

If there are different ways to do this depending on the number of rows I'd be interested to hear each way as it would be great to be able to do this from the source and cut out the initial manual lookup, but I'd also be happy as a starting point to be able to split the circa 50 rows. 

Thanks

[grantjenkins]

If the data was within a defined Table and you were happy for the trigger to be when a file is created (copy the master file into a processing folder for instance), then you could do the following.

For this example, my master file contains a Table with the following data.

See full flow below. I'll go into each of the actions.

When a file is created (properties only) will trigger when we create a new file in the Master Excel Tables folder. You could also configure the trigger to be for a selected file as another option.

List rows present in a table will retrieve the rows from our Table. Note that this will only return the first 256 rows by default, so I've set the Top Count to 5000 (will retrieve up to 5000 items).

Select extracts out all the Departments into a simple array.

Apply to each department iterates over each unique Department. Note that I'm using the union expression to remove any duplicate Departments.

union(body('Select'), body('Select'))

Filter array filters out the rows for the current Department we are iterating over.

Create worksheet creates a new worksheet using the Department name.

Create table uses the Name of the worksheet we just created followed by !A1C1 for the range (3 columns). It also uses an expression to replace any spaces in the Department name for use as the Table name.

//Table range
@{outputs('Create_worksheet')?['body/name']}!A1:C1

//Table name
replace(outputs('Create_worksheet')?['body/name'], ' ', '')

Apply to each row uses the output from Filter array so it iterates over each row for the current Department.

Add a row into a table adds each of the rows into the Department Table. It uses the following expression to get the row data into the correct structure.

{
  "Department": @{items('Apply_to_each_row')?['Department']},
  "Month": @{items('Apply_to_each_row')?['Month']},
  "Revenue": @{items('Apply_to_each_row')?['Revenue']}
}

After running the flow, we should have a new worksheet for each Department with the relevant data.