VBA - generate a new table based on values in an existing table

[g00ber]

Hello,

I would like to use VBA to generate a new table based on the values in an existing table. This would need to be updated by clicking a button to refresh the data in the new table.

Thank you

Existing table. Values are selected from a drop-down list and coloured using conditional formatting:

Generate table using existing table and condition.xlsx
	A	B	C	D	E	F
1	Person	Oct-22	Nov-22	Dec-22	Jan-23	Feb-23
2	A	Green	Green	Green	Green	Green
3	B	Green	Green	Green	Red	Green
4	C	Red	Yellow	Yellow	Red	Green
5	D	Green	Yellow	Green	Green	Red
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:F5	Cell Value	contains "Red"	text	NO
B2:F5	Cell Value	contains "Yellow"	text	NO
B2:F5	Cell Value	contains "Green"	text	NO

Cells with Data Validation
Cell	Allow	Criteria
B2:F5	List	Green, Yellow, Red

New table to be generated in separate sheet:

Generate table using existing table and condition.xlsx
	A	B	C
1	Month	Count of Red	Person
2	Oct-22	1	C
3	Jan-23	2	B; C
4	Feb-23	1	D
Sheet2