Auto deletion of worksheets based on multiple criteria, and automatically returning values

Hi Everyone,

I have been stuck with this problem for a week and I have been trying to code it myself and asking people for help with various parts of the code and sadly I have got to a point where I cannot for the life of me work out how to complete this problem.

I have attached a workbook to make the reading of this easier and hopefully allow people to understand what I am talking about.

I have built code to have a spreadsheet automatically create a new worksheet for every unique item in a specific range in my example these are in Column A of the tab titled Main with the heading reference. Keeping this nice a simple with only three entries, the workbook that I need this to work on has about 100 pages.

In the Main set of data there are two further columns that are used to create these unique workbooks entitled Depot and Holding so that when the macro is run a new sheet is made and the details about the number of holdings in depot and holding are populated on it.


Question 1)
Anyone got any ideas how I can run a macro or have a module that in essence does a conditional sum like sumifs so that the values contained within the worksheet that is entitled GB1, GB2 etc returns the values to mainsheet of the Sheet (GB1 e.g) Resolved column for each depot.

Sorry if that is confusing but as clear as I can make it. I know that it is possible to use sumifs and manually reference the individual sheets but the real world spreadsheet I am trying to automate has too many sheets to make it feasible.


Question 2)
Again using a Macro I want to be able to have excel delete any worksheet which is no longer required, the definition for this would be (again refer to my attached workbook)
VBA checks that every entry under Reference e.g GB1 and that the entry under Total Remaining = 0 for every depot also. The caution here is that if for example the reference GB2 = 0 under total remaining in the Wrap depot but GB2 had either a positive or negative 'total remaining' in the Nominee depot I don't want it to delete the sheet.



Please people help, I cannot work these problems out and whats more can't find the solutions to them in any forum or google search. Can this be done? If so your help in working it out is most appreciated and hope that it provides an interesting challenge for you too. I would love to learn from peoples solutions to this.

Ok so no solutions so far thanks for looking though, for question 2 would it be possible to simply delete workbooks if they existed in a list? So for example I manually wrote GB1 in a seperate worksheet in column A I could run this macro and it would delete the worksheet GB1?

The first post is my ideal but if anyone can think of work arounds or alternative file setups to get me the answers I need I am all ears...

Thanks again!