VBA to control multiple pivot tables

[Harvey12]

Hi All,

I am fairly new to VBA so if you could bare with me it would be much appreciated.

What I would like to achieve is the ability to change the date filter in a whole range of pivot tables in multiple different worksheets in increments of months which take the form of "mmm-yy" EG. APR-15 or May-15. I have around 25 pivot tables and it is fairly time consuming moving between tabs changing the dates at the end of the month.

I have looked for this previously and seen that slicers are an option - but my data does not all come from the same source so as far as I am aware it doesn't work.

Could someone please help me to start to write some form of Macro that changes all of the dates of the pivot tables in all of the worksheets but 1 month.

Or even better could I create a tab of which you type the desired month of choice in the same format (mmm-yy) as the table and this will then run the macro by pressing a button. This will in turn update all the tables to the desired month. Is this possible? Plus how will this be affected in years going forwards?

Any assistance would be very much appreciated.

Many thanks
Harvey

 

[Harvey12]

*bump*

 

[grawls44]

I was looking for something like this not to long ago. This is what I found.

Make a new sheet with a pivot table with only the date field and put this code in the page module. when you change the date on this sheet it will change all pivot tables in the workbook.

Option Explicit
Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
On Error Resume Next
Dim wsMain As Worksheet
Dim ws As Worksheet
Dim wsPTF As Worksheet
Dim ptMain As PivotTable
Dim ptF As PivotTable
Dim pt As PivotTable
Dim pfMain As PivotField
Dim pf As PivotField
Dim pfPTF As PivotField
Dim pi As PivotItem
Dim bMI As Boolean
Dim bPTF As Boolean


On Error Resume Next
Set wsMain = ActiveSheet
If wsMain.Name <> Me.Name Then GoTo exitHandler
Set wsPTF = Sheets("Change Week")
Set ptMain = Target
Set ptF = wsPTF.PivotTables("PT_List")


Application.EnableEvents = False
Application.ScreenUpdating = False


For Each pfMain In ptMain.PageFields
    bMI = pfMain.EnableMultiplePageItems
    bPTF = False
    For Each pfPTF In ptF.PageFields
      If pfMain.Name = pfPTF.Name Then
        bPTF = True
        Exit For
      End If
    Next pfPTF
    If bPTF = False Then
      Exit For
    End If
    For Each ws In ThisWorkbook.Worksheets
        For Each pt In ws.PivotTables
            If ws.Name & "_" & pt <> wsMain.Name & "_" & ptMain Then
                pt.ManualUpdate = True
                Set pf = pt.PivotFields(pfMain.Name)
                        bMI = pfMain.EnableMultiplePageItems
                        With pf
                            .ClearAllFilters
                            Select Case bMI
                                Case False
                                    .CurrentPage = pfMain.CurrentPage.Value
                                Case True
                                    .CurrentPage = "(All)"
                                    For Each pi In pfMain.PivotItems
                                        .PivotItems(pi.Name).Visible = pi.Visible
                                    Next pi
                                    .EnableMultiplePageItems = bMI
                            End Select
                        End With
                        bMI = False
                
                Set pf = Nothing
                pt.ManualUpdate = False
            End If
        Next pt
    Next ws
Next pfMain
    
exitHandler:
  Application.EnableEvents = True
  Application.ScreenUpdating = True
  Exit Sub
errHandler:
  MsgBox "Could not update all pivot tables"
  Resume exitHandler


End Sub

Hope this helps.
Greg

 

[Harvey12]

Hi Greg,

Many thanks for getting back to me with this. As I mentioned above I am fairly new to this and need some guidance. I don't quite understand the bit where you say 'Make a new sheet with a pivot table with on the data field and put this code in the page module'? What exactly is it you want me to do there? Does this mean you want me to create a new tab? Would that tab need a particular name? Plus if you could just give me a brief walk through of the Macro that would be appreciated.

In addition in the section of the code where it is written: Set wsPTF = Sheets("Change Week") Where does that come in? Can that be change month?

Any assistance would be much appreciated,
Many thanks
Harvey

 

[grawls44]

Hi Harvey,

Yes I mean make a new tab you can name it change month but be sure to change week to month in the code.
Create a pivot table on the new tab be sure to name the table PT_List. The only field you need in the table is Date or month whatever the name of the field you are wanting to change. then on the tab left click on the name (Just like you were going to rename the tab) click view code that will open the vba module. then copy and paste the code into this window ( that will put the code on that sheet only) so you can change the date on any other tab and it will not change everything but if you change it on the change month tab the code will go through each pivot table in the workbook and look for that field and change it. The code takes a few min to run but it beats the heck out of having to manually change each table.

I hope this gives a better understanding.
anything else let me know.

Greg

 

[Harvey12]

Hi Greg,

Sorry for being so long before I spoke with you last! So I tried this the other day and it seemed to work okay except with a formatting error, which is based this end so I can sort it.

I'm attempting to do this in the exact same way as above and it is now not working, could there be something I am doing wrong? No new file all exactly the same! I am very much confused!!

But when it was working it was absolutely fantastic so many thanks for all your help, could just do with this last push to get this thing up and running, apologies for any inconvenience.

Many thanks
Harvey

 

[Harvey12]

What the issue with formatting is (Just in the case that you can help, since you seem far more of an excel wiz than me!!): MOST of the dates in the tables take the format 'MMM-YY', and I could say that is true for most of the data, there are thousands of rows which is the difficulty and the data goes as far back as 2001 so much of the data is stored in older formats etc. we have managed to convert most of the data into our new format but some still remains DD/MM/YYYY and because of the hundreds of thousands of rows there are many rows with the same data and we have already tried to reformat these.

For some reasons these dates wont change to our new format.

None the less most of this info is older - even though getting the format to be correct is very much in my interest, more importantly the macro is unable to distinguish between the desired date format and undesired. By that I mean the macro selects the month I desire and then also selects all the dates that are in the format DD/MM/YYYY.

Is there an easy way to either format these dates which I may have not already thought of (I have tried, format painting, formatting the column, changing the format to a custom format). Or if not changing the macro so it does not include rogue data outside of the required format?

Many thanks
Harvey

 

[grawls44]

Harvey,

You got me on that one. Sorry but I don't have a fix for that. Hope someone else can help you out.

Greg

 

[Harvey12]

Hi greg,

Many thanks for all of your help you have been a great asset to my project!

Harvey

 

[Harvey12]

Hi Greg,

I hope all is well.

I have another question for you! Sorry to keep pestering yourself with this! I imagine you have a lot of people you are helping!

From your Macro how easy is it to exclude certain pivot tables? For example there are 3 pivot tables in every worksheet which need excluding? Would it be easier to do it by name? Have them all having the same name? Or perhaps all different names and exclude the pivots with those specific names? Or only update pivot tables with name 'X'? Or since those pivot tables run up to column U don't update pivots past column U?

Is this even a possibility?

Many thanks
Harvey