Insert a value in a cell based on what macro is run

Hi. I have a spreadsheet where I added macros (attached to buttons) that filter information on a long report based on values in 1 column (the information is a department name). There are 7 different macros for the 7 departments. The code I used is

Please Login or Register  to view this content.

The Department is in the array ("D&C" in this instance. "PN" will appear in all the macros) and I named the Macro "Sub DC_Click()". All the other macros follow the same format with the department name changing. What I want to do is insert the Department name in a cell in the Header of the report based on what macro is running. So if the user selects this macro for "D&C", "D&C" will appear in cell E2 (not the "PN"). How would I do this? The code will need to say something like if Sub DC_Click() is run, insert "D&C" in cell E2; if Sub Legal_Click() is run, insert "Legal" in cell E2, etc., continuing for all 7 departments. Thank you.

Thanks Olly. This worked exactly as I asked. Wonderful! I do have another follow up question: I have abbreviated some of the department names that I have in column "V" due to the column width. For example, "Dev Analysis" instead of "Development Analysis". Is there a way to change the code so I can have the full name not the abbreviation? I tried changing "foo" to the full name in the sub but that obviously doesnt work since that is not the value in the filter. Any suggestions? thanks

Ok. I will try that. Thank you again. Enjoy your weekend.

OMG! Perfect! I was just sitting here debating about a lookup table and investigating another code method when your message came through! Perfect! Thanks so much for the assistance. I owe you a virtual beer!