Macro To Adjust The Visible Weeks in a Pivot Table

JasonTrauger

New Member
Joined
Mar 3, 2016
Messages
6
I recently built a dashboard at work that would compare a specific week for 2015 and 2016, in regards to revenue, spots aired, and other financial metrics. The boss of the person for whom I built that dashboard wants to see this in a cumulative sum (i.e., if I choose week 9, it would be the aggregate of weeks 1-9) instead of the current week vs. week setup. The data reads off of a pivot in another tab whose row labels are Region and Week of Broadcast Year. SO, I need a macro that would read a specific cell, which the user types in the desired week number, that would change the pivot table in question to change the Week of Broadcast Year from "all" to, in this example, choosing weeks 1-9.

I appreciate any and all suggested help on this. If the various readers don't have the solution, I'll take suggestions to good books on how to learn about creating macros. This has gone from something that wasn't necessary to a work specific need in a hurry.
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Welcome to the forum. You will get good help here.

What you want to look at is pivotitem.visible

The easiest way to learn about it is to turn on the macro recorder and change them by hand. This will show you what you need to do.

Then you can write some looping code around it to do it based on the week value chosen. Look at the Pivotfield.items.count. Just make sure that your code NEVER tries to turn them all off.

Make sure that before manipulating the items in code you turn off application.calculation and application.screenupdating. Turn them back on once you are done. Otherwise it is very slow.
 
Upvote 0
Well, I tried to create a code based on "if" statements but it hasn't worked out quite like I wanted. Essentially, I did the following:

If Range("Week_Number").Value = "1" Then
ActiveSheet.PivotTables("PivotTable3").PivotFields( _
"Broadcast Week of Year Number").ClearAllFilters
ActiveSheet.PivotTables("PivotTable3").PivotFields( _
"Broadcast Week of Year Number").PivotFilters.Add Type:= _
xlCaptionIsEqualTo, Value1:="1"
Else:

If Range("Week_Number").Value = "2" Then
ActiveSheet.PivotTables("PivotTable3").PivotFields( _
"Broadcast Week of Year Number").ClearAllFilters
ActiveSheet.PivotTables("PivotTable3").PivotFields( _
"Broadcast Week of Year Number").PivotFilters.Add Type:=xlCaptionIsBetween, _
Value1:="1", Value2:="2"

Since I'm dealing with 52 or 53 weeks in a year, depending upon if it is a leap year or not, I pulled that all the way down. The macro itself works except: 1) the macro doesn't automatically run and 2) whenever I get into a week of the year greater than 9 (say, 12 for example), the macro ends up shifting the pivot to show week 1, week 10, week 11, and week 12. I have NO idea what that is but it is frustrating.

I would prefer to make the code work where it turns off every week greater than what is currently listed in the option combo box. But, writing 53 "if" statements is a massive undertaking when each one involved all of the weeks.

Yes, I am a VBA novice and, while you'd think that working for one of the US's largest media companies would put me in a situation where I'd be surrounded by macro/coding wiz kids, that definitely isn't the case.
 
Upvote 0
If you want to have it run automatically, you need to hook the worksheet_change event. To do this, you go into the VBA editor and find the worksheet and double click. You will get a window titled <workbook> - <Sheetname> (Code) and it will default to the selectionChange event. Change the first dropdown to worksheet and the second to Change and it will give you a stub for the event handler.

In the event handler, you want to check that the cell that was changed is the one the user types in. Otherwise the sheet is constantly changing!!!

Code:
Private Sub Worksheet_Change(ByVal Target As Range)


If (Not Intersect(Target, Range("A1")) Is Nothing) Then
    'changed the week number in A1
    
End If
End Sub

Give me another minute and I'll whip up a piece of code for the filtering
 
Upvote 0
You said that the pivot table is on another sheet from where the user types the week number. Is the "Broadcast Week of Year Number" a page field?

This will not be perfect. But it will give you an idea how to do it. (assuming I understand the goal)

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim weekCount As Integer
Dim currWeek As Integer


If (Not Intersect(Target, Range("A1")) Is Nothing) Then
    'changed the week number in A1
    With Sheets("pivottablesheetname").PivotTables("PivotTable3")
        weekCount = .PivotFields("Broadcast Week of Year Number").PivotItems.Count
        For currWeek = 1 To weekCount
            If (currWeek <= Intersect(Target, Range("A1")).Value) Then
                .PivotFields("Broadcast Week of Year Number").PivotItems(currWeek).Visible = True
            Else
                .PivotFields("Broadcast Week of Year Number").PivotItems(currWeek).Visible = False
            End If
        Next
    End With
End If
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,255
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top