1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. 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...

  3. 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

Discussion in 'Ask an Excel Question' started by Carlos Ruano, Apr 17, 2017.

  1. Carlos Ruano

    Carlos Ruano Member

    Messages:
    36
    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

    Attached Files:

  2. vletm

    vletm Well-Known Member

    Messages:
    2,811
    Carlos Ruano
    Something like this ...?
    Make selection from Sheet1's Cell[A1].

    Attached Files:

  3. Carlos Ruano

    Carlos Ruano Member

    Messages:
    36
    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
  4. vletm

    vletm Well-Known Member

    Messages:
    2,811
    Carlos Ruano likes this.
  5. Siga

    Siga Member

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

    Carlos Ruano Member

    Messages:
    36
    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!
  7. bosco_yip

    bosco_yip Well-Known Member

    Messages:
    1,248
    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

    Attached Files:

    Last edited: Apr 18, 2017
    Thomas Kuriakose likes this.
  8. vletm

    vletm Well-Known Member

    Messages:
    2,811

    Attached Files:

  9. Siga

    Siga Member

    Messages:
    38
    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 :)
    Carlos Ruano likes this.
  10. Carlos Ruano

    Carlos Ruano Member

    Messages:
    36

    I experience the same thing exactly.
  11. Carlos Ruano

    Carlos Ruano Member

    Messages:
    36
    Also, another question ... are these methods helping reduce file size, or just reduce the number of sheets in the workbook?
  12. vletm

    vletm Well-Known Member

    Messages:
    2,811
    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?

    Attached Files:

  13. Siga

    Siga Member

    Messages:
    38
    I am in 2007 Excel. Not sure if this is it, but I do not see any difference while choosing different "views". Hope Carlos is more lucky :)
  14. vletm

    vletm Well-Known Member

    Messages:
    2,811
    Siga - snapshot by selections looks here like below
    for me ... those looks ... how You would say? not different?
    Screen Shot 2017-04-18 at 18.21.11.png Screen Shot 2017-04-18 at 18.21.24.png
    Screen Shot 2017-04-18 at 18.21.36.png
    Siga likes this.
  15. Siga

    Siga Member

    Messages:
    38
    My computer doesn't show that and I don't know why.
  16. vletm

    vletm Well-Known Member

    Messages:
    2,811
    Siga - Are Macros Enabled?
    Siga likes this.
  17. Siga

    Siga Member

    Messages:
    38
    Yes, Sir.
  18. vletm

    vletm Well-Known Member

    Messages:
    2,811
    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 ...
  19. Siga

    Siga Member

    Messages:
    38
    hahaha. Thank you :)
    I am a woman - I want to press the button and see the result. If no result - I cry...
    "what-when" is not something I was created for. hahaha! :)
  20. Carlos Ruano

    Carlos Ruano Member

    Messages:
    36
    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.
  21. Siga

    Siga Member

    Messages:
    38
    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).
  22. vletm

    vletm Well-Known Member

    Messages:
    2,811
    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.

    Attached Files:

    Thomas Kuriakose likes this.
  23. vletm

    vletm Well-Known Member

    Messages:
    2,811
    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.
  24. vletm

    vletm Well-Known Member

    Messages:
    2,811
    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
  25. vletm

    vletm Well-Known Member

    Messages:
    2,811
    Carlos Ruano & Siga
    This version would automatic set correct xlPivotTableVersion...
    (... and this still works here )

    Attached Files:

    Siga likes this.

Share This Page