ShogunPatch
Board Regular
- Joined
- May 8, 2009
- Messages
- 52
OK, forgive me for being overly verbose here. I will try and keep this brief but want to include all the info anybody out there with the right knowledge might need to help solve my problem; as such I am going to explain my issue as I would to a five year old - as much for my own sake as for yours!
I have a very large workbook which includes 100 sheets (A) each containing fixed data relevant to one unit
(in this case n = a building; there are 100 buildings), a few sheets (B) containing parameters & settings, and then 1 main sheet (C) which does complex calculations pulling & combining data from sheets (A) based on the parameters in sheets (B). The workbook is so large, (100MB+) that it has to be set to calculate manually.
One of the settings in one of the sheets (B) dictates which building
is currently being looked at. As such, sheet (C) effectively builds a report for building
and so can report on just one building at a time, that building
being determined by a drop-down box listing all of the available buildings and that list is in turn defined in one of the sheets (B).
If I want to get a consolidated picture for all buildings I currently manually change the building choice
in the dropdown box, and then run a macro (m1) which updates the report to show what I want, selects the specific bits of the report I want and copies the selection, switches to a separate pre-formatted consolidation sheet I have created (D), jumps to the last row in that sheet, pastes values and then jumps back to Sheet (C).
I then need to manually change the selection
and then rerun macro (m1).
What I would like is another macro (m2), which automatically loops through the different values for
so that the whole process is automated. Then in a perfect world, perhaps even a separate macro (m3) which would give me a list of tickboxes so that I could select specific properties for which to run the report.
I realise this may be asking a lot, but any help would be much appreciated.
I have a very large workbook which includes 100 sheets (A) each containing fixed data relevant to one unit

One of the settings in one of the sheets (B) dictates which building



If I want to get a consolidated picture for all buildings I currently manually change the building choice

I then need to manually change the selection

What I would like is another macro (m2), which automatically loops through the different values for

I realise this may be asking a lot, but any help would be much appreciated.