I think my VBA code is screwing up my PivotTables

[RollerRagerMD]

I have a bunch of PivotTables that can be filtered by Month and Year. I wrote a UserForm and macro to let a user change the filtered month and year with just a few clicks of a button. Unfortunately, it seems to have stopped working. I noticed that when I run the macro, it starts changing the actual list of months or years in the PivotTables. Here's the UserForm code:

Please Login or Register  to view this content.

And here's an example of the code I use for changing the PTs:

Please Login or Register  to view this content.

When I first started this, if I clicked the Month filter on any of those PTs, I could choose from 3 to 7. Now the only options are 5, 10, 11, and Month (which were all the different selections I made in the ComboBoxes). If I choose something other than those, the program throws an error. Basically, I think that when I change the month in the ComboBox and run the macro, it actually changes the contents of the PivotTables.

Any idea what's going on here and how I can fix this?

[RollerRagerMD]

Anybody? If I refresh my PivotTables, the report filter fields still don't populate with the proper data. I've checked the data source and it's fine. If I add a new PivotTable from the same external data source, it's fine. Why are these PivotTables broken?

[GeneralDisarray]

Can you upload an example? I noticed you are using excel 2010 which means you have access to slicers. Have you considered just making the month or year available to slicers? That would entirely remove the need for the user form and provide the same functionality to your users.

If you can upload a simple example, I'd be glad to have a look.

[JosephP]

cross-posted here: http://stackoverflow.com/questions/1...my-pivottables

[RollerRagerMD]

Well I couldn't figure out how to fix the PivotTables so I just rebuilt them, but I did figure out how to stop VBA from messing with them. I also made my code a lot shorter... I had written each individual table's modification and then realized it'd be much easier and faster with a loop.

Please Login or Register  to view this content.

The bolded lines are what appear to keep it from going haywire. I don't know why it was screwing them up before, I think it had to do with a filter already being applied. It wasn't clearing the filters before. Next step: error handling for when a chosen month is out of a table's range...

[GeneralDisarray]

I made up this quick example, you can connect slicers to multiple PTs (asked about in PM). You do that by going to the "Slicer Tools" > Options > Pivot Table Connections. There, you just check which tables are connected to a slicer.

SEE OPTIONS 2 TAB for multiple tables on the same set of slicers

ALSO: Option1 uses extra data columns to make Month and Year a slice-able field. In Option2 I just used grouping to get the Month/Quarter/Year without needing to add any columns to the data table. Grouping dates like this is yet another built in excel feature.



Check this out. Glad you got the code to work, and you many need no further help, but I really wanted to post this example because this is a perfect case for slicers :D

[RollerRagerMD]

Sweet. That's very helpful. However, I should explain that different PTs are connected to different data sources. For instance, one set of PTs is connected to "CPB Freight" and another is connected to "MDF Freight." When I go into the PT Connections window, it only lets me select the CPB PTs if I started the slicer on a CPB table. Any way around that?

[GeneralDisarray]

You would need one set for each data source, so if you have many sources it might get messy.

[RollerRagerMD]

Yeah, that's what I was thinking in our convo. Personally I'd be fine with it but I'm fairly certain management would ask me to "optimize" the interface