Good Afternoon,
I have created a "data warehouse" where data from various sources is dumped into an excel workbook (each workbook has 31 sheets and is names after the month and year it relates to for example march 2013.xls) I run various reports that cross reference data from each of these warehouse. for example I have a sales workbook with sheets Feb through to Jan pulling the data from the "warehouse". The problem I am finding is that some of the lookups take a bit of time to run and some reports can take upto 5 minutes to open and refresh the formula's therein.
What I'd like to do is have each report open the data warehouse workbooks when it opens so that the lookups and formula's update faster and then close them when calculations are complete. As I'm rather new to VBA and learning as I go, I'm trying to use the code below that i use in another report as a base to start on.
Ideally it would know that the current year is 2013 so would open Feb 2013.xls through to Dec 2013.xls as well as Jan 2014.xls and then close them all after the workbooks have recalculated.
Bookmarks