Results 1 to 7 of 7

Thread: Need Help with Macro to Filter out Months in Pivot Table

  1. #1

    Smile Need Help with Macro to Filter out Months in Pivot Table



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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

  2. #2
    Can you post the workbook with your macro so that we can see what is happening?

  3. #3
    Yes - I just need to clean up any patient related info and I'll post it. Thanks! Molly

  4. #4

    Post 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
    Attached Files Attached Files

  5. #5
    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

  6. #6
    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

  7. #7
    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.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •