Consolidating data from multiple worksheets and filtering based on priority...

[Spiska01]

Completely new to the forum, thanks in advance for your help. I'm trying to concolidate the data from tabs 2-7 on this worksheet into tab one, and then filter based on priority (high to low, 1 to 10). I'm sure this is much easier than I'm making it out to be...thanks again.

[arlu1201]

Try this code

Please Login or Register  to view this content.

Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
Choose Insert | Module
Where the cursor is flashing, choose Edit | Paste

To run the Excel VBA code:
Choose Tools | Macro | Macros
Select a macro in the list, and click the Run button

[Spiska01]

Thank you Arlette,

Much appreciated, quick follow up. The macro consolidates accurately, however, if I edit one of the sheets and run the macro again it does not pick up the added / subtracted entries. For instance, if I add a new project under the GFS tab and run the macro again on the BNY IWM tab it will not register the addition. Is this fixable? Finally, is it possible to include filter functionality into a macro? Id prefer to filter out blank rows and filter based on highest priority (=1). Thanks again for your help.

[Spiska01]

Sorry for the multiple replies. I noticed that each time I run the macro it doesn't update the data, rather it copy/pastes the tabs again. So, I would assume I need to incorporate some sort of clear, then the macro, then a filter by priority. If you view the spreadsheet I've attached I've done that in three macros. Is it possible to combine the code into one? Thank you very much again for your help.

[arlu1201]

This is the edited code which takes care of clearing the BNY IWM sheet and re-populating each time you run the macro.

Please Login or Register  to view this content.

Regarding deleting blank rows, should i check if the entire row is blank and then delete?

What about the filtered data where priority is 1, where should this data be copied?

[Spiska01]

Sorry, I'm not being 100% clear. Thank you for the new macro, the clear and re-run is necessary. With respect to blank rows, they are not important for the aggregation and should not show up in tab one. Ultimately, tab one should show all of the rows with data from the other tabs (all of the projects), prioritized from smallest to largest (1 to 10). So, the three macros I used clear the existing data, update the projects from all the sheets, and then filter the prioritize column from most important to least important (1 to 10, smallest to largest). Make sense?

[arlu1201]

This is the updated code including the part of deleting the blank rows.

Please Login or Register  to view this content.

You didnt answer my earlier question - What should be done with the filtered data? Should the data be copied to another sheet after filtering for priority = 1?

[Spiska01]

Sorry about that. No, the data remains on the same page...just filtered. The goal is to have the most important projects, in other words those with a priority 1, at the top. So the filter action is required but the data stays put.

The last two macros you sent do some weird things to my spreadsheet including formatting changes.

[arlu1201]

The last macro i gave you is the updated version of the previous ones.

It consolidates all the data from the various sheets and then checks for blanks and deletes them. This is much faster than checking for blanks in each sheet before copying.

This is the updated code -

Please Login or Register  to view this content.

By filter, you are not meaning to "sort" the data right? The code that i have given you clears the contents, then populates data, removes blanks and then applies filter where priority = 1.