Macro to group certain pivot tables after refreshed with data

I was looking to create a macro to group two of my pivot tables (by month, year) any time the data is refreshed. When I import the data, months are duplicated and I receive a error saying that I can't overlap pivot tables so if I am able to refresh the data and group through a macro, that would help with this issue. The main pivot tables that are affected are on the 'Open SR Pivots' tab as well as 'Closed SR Pivot'. The "Open/Closed SRs by Month" pivot table as well as "Open/Closed by Severity" will show the duplicates.
DSE-Carelog-Report.xlsm
AckeemKAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saurabh Singh TeotiaCommented:
You are facing this problem because you have selected more range as pivottable source data..Now the range which you have selected has blank values in them and as a result the dates have been getting ungrouped because you can't group them if you have blank values..and once it's get ungroup it gets extended to the pivot which is right below it and which generate the error what you are saying..

As a best alternative if you want dynamic range for pivot..create a name manager range which is dynamic in nature and make pivot table on the top of that range..

Enclosed is your workbook for your reference where i have made it a dynamic range which will auto adjust itself once you add or remove the data and all you need to do is refresh and your grouping will be still be intact as a result you won't get any error...

Saurabh...
DSE-Carelog-Report.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AckeemKAuthor Commented:
Hi Saurabh,

I keep receiving this error every time I try to refresh the data.
Screen-Shot-2015-04-21-at-8.13.34-AM.png
0
Saurabh Singh TeotiaCommented:
I'm not sure what you are doing here..Can you help me connect with what you are trying to do here...

As if you refresh the pivot tables in the file which i gave to you it works perfectly...
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

AckeemKAuthor Commented:
Yes, I seen the issue on my side. Lastly, for the Y values in my "Closed SRs by Severity" chart, I keep receiving this error as I am trying to capture the data in a dynamic range within the pivot table. Any help here?
Screen-Shot-2015-04-21-at-9.07.43-AM.png
0
AckeemKAuthor Commented:
I am trying to capture the years and the month within the chart since I have 2014 and 2014 data but it seems like the offset function doesn't allow it.
0
Saurabh Singh TeotiaCommented:
Ackeemk,

I'm not sure what you are doing here..Can you post another question for this with an example of step by step what you are doing and what problem you are facing so that accordingly able to provide you a solution for the same...

Saurabh...
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.