Forum Discussion

MattyNt's avatar
MattyNt
Copper Contributor
Oct 28, 2022

Macro to change sheet displayed

Hi all,   I have been given a workbook that contains plenty of tabs that even my navigation macro feels a little pointless as they're similarly named. Each tab is a month showing yearly data, with ...
excel
Macros and VBA
MattyNt's avatar
MattyNt
Copper Contributor
Oct 31, 2022
mathetes
Please find link to the file on Google drive below. I havent made any significant changes, this is how it is all set up and laid out. Certain fomulas are not yet complete but that won't impact anything.
https://drive.google.com/file/d/1NBWpLNdZbH4j63AOBOjXsSrIjyxQqdWM/view?usp=drivesdk
mathetes's avatar
mathetes
Silver Contributor
Oct 31, 2022

MattyNt 

Oh, my. I strongly (STRONGLY!!) recommend that you combine all those monthly sheets into a single, ongoing database. Just add a column that contains the effective date of the data on that row. From such a re-designed database--unless I'm missing something--you'll be able to do all the analyses you want to do, certainly including year-over-year changes in each month. Are you open to such a change (or in a position to do so)?

  • MattyNt's avatar
    MattyNt
    Copper Contributor
    Nov 01, 2022
    mathetes

    That's what I WANT to do. As with other visualising tools it isn't necessary. However it needs to be something that can be easily viewed still hence the 2 sheets per month. Thats why I had the request to pull to and clear one singular sheet.
    But yes the other way I had in mind as a last resort and see how it goes down is to combine it all and just filter by month as the key is to be comparing each month's history.
    • MattyNt's avatar
      MattyNt
      Copper Contributor
      Nov 01, 2022
      mathetes

      Ah, I recall now. Previously when I've looked at doing this, it messes with my formulas for comparisons to the previous month. Is there a way I can alter them to adapt to filtering??
      • mathetes's avatar
        mathetes
        Silver Contributor
        Nov 01, 2022

        MattyNt 

        Previously when I've looked at doing this, it messes with my formulas for comparisons to the previous month. Is there a way I can alter them to adapt to filtering??

         

        Tell you what. Why don't you write out all of the current goals or objectives for this workbook. What are the outcomes, results, outputs (however you want to say it, whichever term is most appropriate)?

         

        Working with a single, well-designed database is far and away the most solid foundation you can have for what would appear to be your situation. Excel has marvelous tools to parse data, enabling comparisons like

        • month-over-same-month-in-prior-year
        • month-over-prior-month
        • quarter-over-quarter
        • etc

        The Pivot Table, for one, would enable you to do quite a bit of that. But also FILTER, functions like DGET (one of several data table extraction functions)

         

        But let's begin with a full description of what you need in the way of output from all this input.

Resources