• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Consolidate 3 sheets with pivot tables into one

Hi, I have a workbook that I create for Project Managers on a monthly basis. There are several sheets in it, and I am trying to find a way to consolidate it a bit if possible. One of my thoughts is to use only one sheet for three pivot tables. I do not want to have the pivot tables one below the other on the same sheet. What I want instead (if possible), is to have one sheet with a pivot table that can be changed with the click of a button.

In other words, right now I have three sheets with pivot tables. Those sheets are called, "Hrs by Dept & Div per Month", "Hrs by Activity per Month", and "Activity by Employee by Month". What I want instead, is one sheet called "Pivot Tables", and three buttons at the top called, "Hrs by Dept & Div per Month", "Hrs by Activity per Month", and "Activity by Employee by Month". When I click on any of the buttons at the top, the pivot table will transform into the pivot table by that name. I hope this is clear! :)

Is what I want to achieve possible? I have attached a sample file

If VBA is the only solution, then I am fine with that, but I am hoping to see solutions that do not use VBA, too!

Thanks!
Carlos
 

Attachments

  • ThreePivotTables.xlsx
    623.4 KB · Views: 6
Carlos Ruano
Something like this ...?
Make selection from Sheet1's Cell[A1].

Yes, that will do just fine! Thank you, vletm. I think I owe you a cup of coffee by now. Probably more like a whole carafe:p

Could you point me in the direction of how to learn what it is that you did? At least, name the process steps, and I can research them.

Thanks again!

Carlos
 
hmm..does it really work? I make a selection, but the view is the same even after enabling the content.
 
hmm..does it really work? I make a selection, but the view is the same even after enabling the content.

It does work for me. I am still interested in learning if there is a non VBA way of accomplishing something similar.

Very grateful to vletm!
 
.......if there is a non VBA way of accomplishing something similar.........
Another option,

Here is a non-VBA and using Hyperlink way as per attached file.

3 buttons are done by cells background color and border lines.

Regards
 

Attachments

  • 3PivotTablesHyperlinkWay.xlsx
    612.8 KB · Views: 3
Last edited:
Carlos Ruano
I just notice few things to modify ...

I know it is not my "problem", but still want to check your miracle, vletm. Anyway, now trying to choose from the drop down meniu I receive a message

Run-time error '9'
Suscription out of range
End Debug Help

Choosing Debug it throws me to your macros. Trying to exit, everything dissapears in excel sheet of 3 combined Excel tables.

Sorry for making a mess :)
 
I know it is not my "problem", but still want to check your miracle, vletm. Anyway, now trying to choose from the drop down meniu I receive a message

Run-time error '9'
Suscription out of range
End Debug Help

Choosing Debug it throws me to your macros. Trying to exit, everything dissapears in excel sheet of 3 combined Excel tables.

Sorry for making a mess :)


I experience the same thing exactly.
 
Siga & Carlos Ruano
Yes ... if clean 'too much' and do not test enough ... then what ever :(
There were 'typo' - sorry!

Now it would work without those three pivotsheets!
'method':
smaller file size
You can see only one sheet in time - or how?
... why need all pivots separate sheet?
Why need to do almost three same kind of Pivots, if 'one is enough'?
No need to worry about 'refresh' pivot - just go to sheet (refresh automatic) and if need another view then 'just' select and see refreshed pivot at once.
... if every pivot has own sheet ... gotta remember to refresh...
if needs more same kind of sheets then 'modify code'.
... maybe there are more, but just now I could list only those.
> Ideas ... questions?
 

Attachments

  • ThreePivotTables.xlsb
    283 KB · Views: 5
Siga - as interesting case as Your Polar Bears ...
maybe Excel-versions have something ...
it's challenge to solve that now.
Have You tried to run that macro step-by-step?
Then You could see what - when ...
 
This is not working for me either. The last time you helped me, vletm, I had to restart my computer before the macro would work. I will try that again later.
 
This is not working for me either. The last time you helped me, vletm, I had to restart my computer before the macro would work. I will try that again later.

Carlos gave me an idea, so insetad of opening it, I tried to save to my computer and open from there. It gives the same error, just saying "Run time error '5' this time (not 9).
 
Siga - who press the button?
Of course, You have to save that file to Your computer before use it.

Carlos Ruano - For me it works ... but #2 Reply file worked ... or how?

both: I tested and tested ... it works here (uploaded that file too).
... it's challenge to edit if no information ... what!
That's why step-by-step is sometimes need to do.
It is miracle if some would be 100% ready in the 1st time.
 

Attachments

  • ThreePivotTables.xlsb
    293.6 KB · Views: 1
Carlos Ruano & Siga
I found one possible, how to solve this 'work-no work' case.
Both of You can record Macro ... do one of those Pivot-table to empty sheet
After that test that it really works and
send that Macro for me.
I try to compare mine and Your versions.
 
Carlos Ruano & Siga
I found ... maybe ... then solution:
Different versions of Excel need own settings:

Find next line from code
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:="Timesheet!R1C1:R" & y & "C22", Version:=xlPivotTableVersion14).CreatePivotTable TableDestination:="Sheet1!R3C1", TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion14

Change those TWO
xlPivotTableVersion14
to
xlPivotTableVersion12
or if still no work then
to
xlPivotTableVersion10
 
Back
Top