Mollinator
New member
- Joined
- Dec 2, 2013
- Messages
- 4
- Reaction score
- 0
- Points
- 0
Hi all,
I'm an intermediate Excel user and I have a selection of reports that I run monthly for a group of non-excel users. I present the reports as really simple pivot tables. The column is a list of hospitals, doctors, and medical practices, the rows are the different fiscal years I am comparing, and the values are a count of how many times each practice has made a referral to my agency. I have added a month field in the column of the pivot table so that each referral source is grouped by month. My problem is that the people using the report want to be able to remove the months when needed and put them back in, easy right? Well, they couldn't understand how to remove the month from the pivot table field list or how to use a basic filter to unselect the months and then reselect when they wanted the months back. (I hope this makes sense so far!)
So my solution was to just record a macro where I turned on the filter for the months, then unselected the months from the list, then another macro that just selected all in the filter list. I put each macro on a button and it worked perfectly at first - they just had to click a button to remove the months and then click the other button to add the months back in.
Here's where my macro backfired - when I applied it to a new list the following month, it filtered out all the months except the new month, plus any new referral sources were missing. When I looked at the macro formula, I found that it only included what was left "selected" in the list after the months were unchecked in the fliter list, instead of showing that I unselected "Jan," "Feb," "Mar," "Apr" etc. So I can only use the macro with the exact same referral sources and months or it will not work properly.
Is it possible to write the macro to only "unselect" certain words, like months, and use it on any list that might have the months in there? I can send a sample if that helps. I know this is probably really simple, but I am not sure how to tinker with the macro formula itself to make it work.
Many thanks,
Molly
I'm an intermediate Excel user and I have a selection of reports that I run monthly for a group of non-excel users. I present the reports as really simple pivot tables. The column is a list of hospitals, doctors, and medical practices, the rows are the different fiscal years I am comparing, and the values are a count of how many times each practice has made a referral to my agency. I have added a month field in the column of the pivot table so that each referral source is grouped by month. My problem is that the people using the report want to be able to remove the months when needed and put them back in, easy right? Well, they couldn't understand how to remove the month from the pivot table field list or how to use a basic filter to unselect the months and then reselect when they wanted the months back. (I hope this makes sense so far!)
So my solution was to just record a macro where I turned on the filter for the months, then unselected the months from the list, then another macro that just selected all in the filter list. I put each macro on a button and it worked perfectly at first - they just had to click a button to remove the months and then click the other button to add the months back in.
Here's where my macro backfired - when I applied it to a new list the following month, it filtered out all the months except the new month, plus any new referral sources were missing. When I looked at the macro formula, I found that it only included what was left "selected" in the list after the months were unchecked in the fliter list, instead of showing that I unselected "Jan," "Feb," "Mar," "Apr" etc. So I can only use the macro with the exact same referral sources and months or it will not work properly.
Is it possible to write the macro to only "unselect" certain words, like months, and use it on any list that might have the months in there? I can send a sample if that helps. I know this is probably really simple, but I am not sure how to tinker with the macro formula itself to make it work.
Many thanks,
Molly