Forum Discussion
Excel PAINFULLY slow - Why?
Just out of curiosity, how many moons is the "many moons" you refer to? Assuming by "moon" you mean something like month, and since you've created monthly sheets (a major design mistake, from my perspective), that could be a factor if those "many moons" add up to, say, 25 years (which would be a workbook with a minimum of 300 sheets).
I have a budget workbook with a single sheet containing ALL of the transactions for my "many moons" worth of income and expense. I let Excel do the "heavy lifting" of summarizing monthly data (using Pivot Table). And the Pivot Table has the feature of enabling easy drill-down to all the backup for any given cell if I want the details on what I spent in, say, "Entertainment" in March of 2020. The "Filter" function on Pivot Tables also enables easy selection of a single year of multiple years to summarize from that consolidated, many moons worth of data, database.
So if I were you, I'd take all those individual monthly sheets and consolidate them into a single sheet containing a single database of all your income/expense history, remove the macros--really unnecessary.
I've attached a very simple sample of how Pivot Tables serve as but one example of Excel's ability to work with a single consolidated database. There are other ways--especially with the new dynamic array functions (UNIQUE, FILTER) to use Excel very effectively to sort through and summarize data such as you're dealing with, so long as the data is all in one table.
Yes, moons is just an expression, meaning many, many, many, months. Like I said, I was using excel 97.
And let me explain that I had only 12 tabs (one for each month). And I did my budget planning and changing in the "worksheet" budget window. My worksheet, had an area for monthly budget, and another area to manage cash flow. Once updated, it was copied to the month tabs for current month and following months.
I will look at your pivot table example though. I like to learn new ways.
But I have found the problem. I had some check boxes. Two boxes for each budget line item. I used those to check whether an item was paid in first half of month or latter. This amounted to about 40 check boxes. However, the way I was doing the copy was to copy everything from the "worksheet" to the individual month tabs. A really bad idea. Because everytime it copied, it just piled more checkboxes, onto the sheets. It didn't replace the others. So, after a long time of use, and copying just for test, I had thousands of check boxes on each spreadsheet. I have completely removed all the checkboxes and no longer need them.
I also had some external references that weren't needed. I've removed those too, but it was the checkboxes that were my problem.
After cleaning up the spreadsheet, it now opens almost instantly, and closes just as fast. The macros are speedy.
Thanks for the quick input. I will take a look at using pivot tables. My budget form is actually very simple form.
By the way, my filesize went from 42Mb to under 200Kb