Adding totals of projects in different stages by a VBA search function into dashboard

Good Day,

I hope this question finds you all well.

I have a workbook where I want to create a dashboard in a proper Userform of the results of my VBA search.

I have attached an example sheet.

I have different sheets where projects are regularly added, updated, and removed from.

I would like to total the amount (R) for each project gate (e.g. Pre-CRA, CRA, DRA) and the total number of projects for each project gate into a dashboard as well. The Userform will look basically like the "Dashboard" sheet I have added as the example.

I have no idea of writing the VBA code to do the search for each row and totaling the number for the different gates.

Your help would be highly appreciated.

Kind Regards,
Raymond

You could use this code, right click on the bottom on Dashboard sheet name, choose view code, paste the code on the bigger winbdow on the right, then click inside the code and press Play or F5 to run it:

Please Login or Register  to view this content.

Regards,
Antonio

Hi Antonio,

Thank you, I will try and let you know.

Hi Antonio,

The code works like a charm. Thank you very much!

Just one question, how would I change the code to run only on specific sheets and not all sheets in the workbook?

thanks!

Hi Antonio,

Sorry, just another question on the code.

As per the attachment, if you want to include more filters to populate two different table how would the code change?

In the end, I have projects that are constraint and unconstrained. I would like to split those in to two separate table as per the example.

I already changed the code to include the "Opportunities" projects as well.

Thank you very much!

See attacned file, I used this code:

Please Login or Register  to view this content.

Regards,
Antonio

Try

Please Login or Register  to view this content.

Hi Jindon, thanks for your code as well. It also work great. Can you perhaps, as per the example, show me how you can code in to split the results into the two different table for unconstraint and constraint projects? It would be much appreciated.

Antonio, thanks also for your sheet. For some reason when I run the code my excel freezes. It becomes non responsive. I assumed it worked perfectly on your side?

Try the attached

Please Login or Register  to view this content.

Hi Jindon, Wow man, that is perfect! Appreciate it very much.

Just one final question then all is solved.

As per the my previous example sheet, is it possible to keep the order of the columns name in this order "Opportunities, Pre-CRA, CRA, DRA" ?

Thanks!

Are there always only 4 items?

Yes that is correct.

Replace "test" sub with the following

Please Login or Register  to view this content.

Hi Jindon, than you so much. That is perfect. Thanks for the fast response to my questions.

I appreciated it very much. Enjoy the weekend!

Hi Jindon,

I hope this message finds you well.

Once again thank you for assisting a few weeks ago with my code.

I tried by using the pilot code you have send me to add to my full project sheet, but I Honestly do not know how to adjust all the code for my sheet.

I have 4 sheets within the workbook that I would like to total in the dashboard sheet as previously. (as per the attached file)

One problem that I also have is that the sheet called "Transmission" has additional columns to total as per the dashboard layout.

To recap, basically like before, I would like to sort the projects between active and future projects, per gate (Opportunities, Pre-CRA, etc.), total the projects costs, the number of projects, the total MW, km, and MVA.

I hope you would be able to help me if possible. I wish I had the experience in understanding all the code. Your assistance will make my life so much easier and will eliminate human error by doing it manually all the calculations.

I thank you in advance.

Kind Regards,

Raymond

Absolutely no idea about MW, Est. Peak Jobs

Please Login or Register  to view this content.

Hi Jondon,

Thanks, for your assistance. I have used your code to run a few times by just changing the column references and then summarized all the results onto one sheet.

Nonetheless, I managed to get the results that I wanted, although perhaps not thought the most effective and shortest way.

Thanks!