I’ve currently making a workbook in which I have about eighty sheets, each relating to a specific customer, and two or three other sheets summary sheets which analyse the whole portfolio.
In one of these summary sheets I want to pose a few (kind of) questions that look like
1. The percentage of income generated by ________ that comes from _______ fees is _____%
2. …
3. A list of companies who’s exposure from ______(K5)__ is greater than___(M5)_____;
…….and that sort of thing, where into the cells that look like_____ I can type different criteria and so return different answers in the answer space.
I’m having trouble with the list part. I’ve no idea how to write macros and I think what is required here is a macro that cycles through every worksheet in the workbook.
Each worksheet is named after its company. The layout of all worksheets is the exact same. In the column D is the names of the sources of income and in column H is the amount of income generated by each source. Some, in fact most, cells in the H column are zero.
I want to write a macro that takes the words that I’ve typed into the cells K5 and M5 and searches each sheet in turn for these words, selects the number parallel to each word, compares the two numbers, and if figure A is bigger than B returns the name of the current worksheet into the column O in the summary worksheet where I want my list.
What I’m finding particularly difficult is thinking of how to get the first company that fits the criteria into the first cell of the column, and then even though there may not be another match for a number of sheets after that, the second company that fits the criteria into the second cell.
If it would help people get a clearer idea of what I want to happen I can post the macro that I've written so far, but I have a feeling it may actually impede the process of understanding, due to my nonexistant vba skills.
If anyone has a solution I’d really appreciate it! Thanks a million
Kate
In one of these summary sheets I want to pose a few (kind of) questions that look like
1. The percentage of income generated by ________ that comes from _______ fees is _____%
2. …
3. A list of companies who’s exposure from ______(K5)__ is greater than___(M5)_____;
…….and that sort of thing, where into the cells that look like_____ I can type different criteria and so return different answers in the answer space.
I’m having trouble with the list part. I’ve no idea how to write macros and I think what is required here is a macro that cycles through every worksheet in the workbook.
Each worksheet is named after its company. The layout of all worksheets is the exact same. In the column D is the names of the sources of income and in column H is the amount of income generated by each source. Some, in fact most, cells in the H column are zero.
I want to write a macro that takes the words that I’ve typed into the cells K5 and M5 and searches each sheet in turn for these words, selects the number parallel to each word, compares the two numbers, and if figure A is bigger than B returns the name of the current worksheet into the column O in the summary worksheet where I want my list.
What I’m finding particularly difficult is thinking of how to get the first company that fits the criteria into the first cell of the column, and then even though there may not be another match for a number of sheets after that, the second company that fits the criteria into the second cell.
If it would help people get a clearer idea of what I want to happen I can post the macro that I've written so far, but I have a feeling it may actually impede the process of understanding, due to my nonexistant vba skills.
If anyone has a solution I’d really appreciate it! Thanks a million
Kate
