How can I separate data from multiple sheets in excel into 1 tab based on a column in the sheet?

Question

How can I separate data from multiple sheets in excel into 1 tab based on a column in the sheet?

Deiotte, Emily 20

I have an Excel file with different sheets for various locations, each containing project data in different states, some are quoted and some are not. I want to create a new tab where all of the quoted projects from different sheets are automatically listed. How can I extract all of the quoted projects, regardless of which sheet they are on, into a new tab based on their status column value? Additionally, what would be the most efficient way to update the new tab if there are changes to the quoted status in any of the sheets?

Barry Schwarz 3,746 Reputation points

2023-06-23T18:16:05.7366667+00:00
In an Excel VBA macro:

You can create a new worksheet using the Worksheets.Add method.

You can loop through all the worksheets in a FOR loop using the Worksheets(x) syntax.

You need an automated test to determine the quoted status of a worksheet. E.g., does a particular cell have a (specific) value. If this worksheet is quoted: Copy the desired data. Paste it into the new worksheet

Next worksheet

Exit

Accepted answer

0 additional answers

Your answer

Barry Schwarz 3,746 Reputation points

2023-06-23T18:16:05.7366667+00:00

In an Excel VBA macro:

You can create a new worksheet using the Worksheets.Add method.

You can loop through all the worksheets in a FOR loop using the Worksheets(x) syntax.

You need an automated test to determine the quoted status of a worksheet. E.g., does a particular cell have a (specific) value. If this worksheet is quoted: Copy the desired data. Paste it into the new worksheet

Next worksheet

Exit

Answer 1

Emily

If your sheets data are (or can be) formatted as Tables you can easily to this with Get & Transform aka Power Query with no advanced understanding of Power Query:

#1 Setup a query (as connection only) that combines all (or selected) Tables in your workbook

#2 Create another query that references (refers to) the above one. Filter the table on the column that indicates if a project is quoted or not

==> The filtered query will load to a new sheet as a Table

#3 Edit the connection only query (above step1) to filter out the Table created by above step2

Then on your demand (or auto. every n minutes and/or when the workbook opens) refresh the resulting query to get up to date info. from the sheets' Table

and that's it

That's efficient and having data structured as Tables allows making almost any analysis you can think of...

If interested I can provide a sample as long as you can post a picture showing one of your Tables (or better share - via OneDrive, Google Drive.... - a representative sample workbook ) + I'll need to know the column name to filter and to filter on what (y/n, yes/no, 1/0...)

Deiotte, Emily 20 Reputation points

2023-06-26T14:12:33.1733333+00:00

Thank you! This is exactly what I was looking for!

Share via

How can I separate data from multiple sheets in excel into 1 tab based on a column in the sheet?

0 additional answers

Your answer