VBA to lookup and sum across multiple worksheets

[juriemagic]

Hi good people!, I have posted yesterday on MrExcel, but no response was received. The link is this: https://www.mrexcel.com/forum/excel-...orksheets.html

The reason I want VBA to do the summing is because the people I do this spreadsheet for, are very weak when it comes to formulae and to be honest, they do not even know about the existence of VBA. further to this, their pc is extremely slow, so to build in more than 1000 formulas to do the vlookup and summing, causes their pc to calculate in circles for a very very long time. so vba really is the answer. I have a piece of code already in place which supplies me with the unique list of names across all 31 pages, I would like to extend this code to also do the summing of the values for all these names across the pages. Not all the pages have all the names, so, some lists are shorter/longer than others. is there a way to accomplish this?..Any and all help will be accepted with great humility and appreciation!!

[CK76]

It would help if you can upload sample workbook. Showing expected output as well as source data.

I'd imagine you can use "Scripting.Dictionary" to extract unique list and to do sum operation.

[juriemagic]

Hi CK76,

I have attached. Thanx for your assistance...

[CK76]

What do you need to sum?

[juriemagic]

Good morning,

The owner of this book had already summed the various customer values on each sheet. Lets refer to these columns as the secondary total columns. Just to clarify, usually the sheets will run Sheet1 to Sheet31, and not Sheet10 to Sheet31 as it currently is. For now, if you could give a code to sum sheets10 to Sheets31, that will be great, I will later just add to your code the other sheets for next month.

another thing I see is that the owner had summed the values for the customers on each sheet in a different column. Now, obviously I believe that we should use the same column on each sheet, preferably a column that is quite far to the right. Then, what you can do is delete Sheet32, click the macro button on Sheet "May" and Sheet32 will be created again, with a unique list of numbers. What I want extra would be that in column B of Sheet32, I also want the names of the customers, then in column C the totals of each customer of all the sheets. So the secondary total columns are summed, or added together, for each customer and the primary totals (Final totals), should then appear on Sheet32 Column C.

If anything else is unclear, please feel free to ask, thank you very very much for your time!!

[CK76]

Hmm, you are missing secondary total columns in Sheet27~31. For demo purpose, I've used Sheet26 to replace each of those sheets.

Also, many of the worksheets had last few rows missing secondary total calculation. For any sort of code to return consistent and accurate result, keeping standardized data structure on each sheet is the key.

Here's the code. See attached as well. FYI - Instead of deleting sheet32 each time, code just clears contents of the sheet.

Please Login or Register  to view this content.

[juriemagic]

Mama Mia!!!, CK76!!!!

Gosh, the code works beautifully!, I am just a little stumped with the following: Which column is used in all the sheets for the summing?..where do I see that in the code? I might have to change the secondary totals column if the owner wants it. Yes, sheets 27-31 did not have secondary columns, as the owner had done his calculations up to the 26th only, (This was the same date I received the file to work on it)...
last thing, I can now add sheets, name them all 1-31, put some data into them, use a secondary total column, and run the code....it will look at all 31 pages?

[CK76]

....it will look at all 31 pages?

The code loops through each worksheet and checks if Sheet name is numeric (i.e. 1 to 31). So yes, it will work for all 31 pages.

Which column is used in all the sheets for the summing?

Basically, by putting contiguous range of each sheet into variant array, I'm using UBound(myArr, 2) to check for right most column and using that to add up the numbers contained there.

Please Login or Register  to view this content.

[juriemagic]

Wow, this is so clever!!!, CK76, you will never, not even remotely ever understand what you have done for us, so much time is saved!..Thank you very very much!!!..Have a wonderful blessed day!!

[CK76]

You are welcome

[juriemagic]

Good morning CK76,

Please forgive me to hassle you with a code you helped me with last year. I have tried to make changes to this code but obviously failed every time. . I am wanting to use this same code in a different workbook, this time for my own purposes. Currently the code looks for names in 31 sheets, and cosolidates them onto the consolidation sheet. What I have found is that if I have a cust. ID number in sheet 1, A5, for example, and his name in B5, and in sheet 4, I have again his ID and name, but his name this time is mispelled, I get this same person twice in the consolidation sheet. I want the code to ignore the name (Column B), and remove duplicates ONLY based on column A. Would you please just teach me how?..

Just something to keep in mind is that the summing of the totals in the consolidation sheet must also be based only on the ID number in column A...Thanx a lot CK76!

[CK76]

Please upload sample workbook. However, I'm currently quite busy juggling multiple projects.
You may have better luck with getting help, if you start a new thread referencing this thread in the post. Since your original question was solved.

[juriemagic]

I was just concerned that if I did start a new thread and you were not aware of it, the people who then did look might not be familiar with how to change it. But, I hear what you say, I'll start a new thread. Thanx a lot!!