Automating pivot table reports through macros

Hi ppl,
I'm trying to automate generating pivot tables from a data source. I'm attaching an example snapshot of what one report looks like. This particular report is for just one member_state and one kind of case_type. I was wondering if I can write a macro telling excel to generate similar reports for other states and second case_type? Also, it will be better to have different files for each state with two sheets for each case type. Ignore the lack of numbers/data in the pivot table as this is for demonstration purpose only. Thanks in advance!