Need Help with Macro to Filter out Months in Pivot Table

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
 
Can you post the workbook with your macro so that we can see what is happening?
 
Yes - I just need to clean up any patient related info and I'll post it. Thanks! Molly
 
Workbook with macro

I hope this attaches correctly. I realized that I made one mistake in my original post - my macro isn't used on an actual pivot table, I just copy the pivot table data after I've filtered it to what I want, and paste it as just a regular table into a new worksheet. Then I run the macro on that. It's then just supposed to filter the months out, at least that was my intention. It was just a recorded macro.

Thank you for taking a look,
Molly
 

Attachments

  • Sample Table with Filter Macro MJJ.xlsm
    80.8 KB · Views: 50
Try this code Molly

Code:
Sub RemoveMonthFilter()'
' RemoveMonthFilter Macro
' Removes the Month from the referrals for business development reports.
'


    Application.ScreenUpdating = False
    
    With ActiveSheet
    
        .Columns(2).Insert
        .Range("B1").Value = "Filter"
        .Range("B2").FormulaArray = "=ISNUMBER(MATCH(A2,UPPER(TEXT(DATE(1900,ROW(INDIRECT(""1:12"")),1),""MMM"")),0))"
        .Range("B2").AutoFill .Range("B2").Resize(.Range("B2").CurrentRegion.Rows.Count - 1)
        .UsedRange.AutoFilter Field:=2, Criteria1:="=FALSE"
        .Columns(2).Hidden = True
    End With
End Sub


Sub BringBackMonths()
'
' BringBackMonths Macro
'


    Application.ScreenUpdating = False
    
    With ActiveSheet
        
        If .Range("B1").Value = "Filter" Then
        
            .Columns(2).Hidden = False
            .Columns(2).Delete
        End If
    End With
End Sub
 
Hi Bob,

This worked perfectly! I was able to apply it to several types of lists with perfect results. Thank you so much - can't say how much I appreciate this. Someday I plan to learn the coding, I would love to know how to do these things myself someday.

Take care,
Molly
 
It's not just code there Molly, I injected a (quite) complex array formula to work out which rows. I insert a column then add this array formula
=ISNUMBER(MATCH(A2,UPPER(TEXT(DATE(1900,ROW(INDIRECT("1:12")),1),"MMM")),0))
which gives TRUE or FALSE depending on whether column A is a month or not, then I filter to hide the TRUE rows, and delete my inserted column. Not tricky, but you just need to be aware of what Excel can do.
 
Back
Top