Creating a summary table in VBA

Why not use a pivot table? First of all, clean up the data to get a consistent spelling of "In Progress." I noticed that each status had a trailing space.

Then convert the data into an Excel Table for two reasons: Excel Tables "remember" formulas and copy them down automatically and they know how big they are so pivot tables and formuals built from them always address the exact amount of data they need.

I added three helper columns in columns F:H: one for each status. They have formulas like: =IF(TRIM([@Status])="Approved",1,0) - the reason for the TRIM is because of the trailing spaces in the data. I am assuming that you are copy and pasting data in and these may have the trailing spaces in there. So if you clear the data table and then copy/paste data into columns A:E (data only do not copy in the headers). Then columns F:G will fill in automatically.

Then it's a matter of building a pivot table with this data. I created calculated fields based on the three helper columns. I also threw in a slicer. Slicers are just fancy filters and are easy to do. Click in the pivot table then select Insert->Slicer from the ribbon. Do a web search on slicers and you'll get several examples of things you can do with them. Slicers make it easier to see what you have selected instead of "Multiple Items."