VBA: Group Pivot Dates By Year
Hi All,
From everything I've read on this forum and other sites, the below code should work, but I'm getting a runtime error 1004 - Group method of range class failed?
Any advice appreciated
Please Login or Register to view this content.
All the best
V
Oh golly this must be a tricksy one I think
I did a record Macro to group the dates and this is what it provided. Compare the results to yours
Please Login or Register to view this content.
The difference is in the last True which the recorded Macro indicates is False.
Excel 2016 (Windows) 32 bit
Count of Fruit Column Labels Row Labels Oct Grand Total apples grapes oranges Grand Total
Sheet: Sheet1
Last edited by alansidman; 09-20-2020 at 09:46 AM.
Alan עַם יִשְׂרָאֵל חַי
Change an Ugly Report with Power Query
Database Normalization
Complete Guide to Power Query
Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
Hi Alansidman
Thanks for the reply
I do actually want the last one to be True, so that it groups by year
I tried adding the select statement shown in your code above, and sure enough it worked
But I really don't want to use a cell reference if I can help it.Please Login or Register to view this content.
Otherwise if the user adds rows above the pivot table the cell reference well change and the macro will no longer work
Is there any way to group a pivot by year without specifying a cell reference?
All the best
V
How about giving it a range name and referring to that range in your script?
I'm not sure I follow?
The pivot table has a name. Is it possible to reference that?
Hi V,
Try this ...
Please Login or Register to view this content.
If I was able to help, you can thank me by clicking the * Add Reputation under my user name
I was holding my breath as I stepped through that sub
Thank you so much! It works perfectly
Glad to help
If the provided solution takes care of your original question, please take a moment & mark this thread as [SOLVED] from the Thread Tools above your first post & consider * Add Reputation to those who have helped by clicking the star icon under their usernames
Administrative Note:
We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.
Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important.
(Note: this requirement is not optional. No help to be offered until the link is provided.)
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks