gather data from mulitple sheets

[Qppg]

I currently have code that looks at a specific sheet and pulls data and places it on a summary page. The current code looks at two criteria for uniqueness then outputs the data accordingly. In my workwook I have 12 sheets that represent the months of the year. The layout for those sheets are identical from one to the other. What I would like to do is modify the existing code that I have to look through the 12 sheets and pull the appropriate information. I am hopeful that this will be relatively easy, and I feel that I should know how to do it but, I am drawing a blank. Any help would be greatly appreciated.

Please Login or Register  to view this content.

Thanks in advance for your help
Bob

[JBeaucaire]

The concept is basically:

Please Login or Register  to view this content.

So maybe this will work:

Please Login or Register  to view this content.

[Qppg]

Thanks JBeaucarie I tried what you suggested and it does increment through the sheets, but now I get subscript out of range. It blows up 3/4 of the way through the 5th worksheet.

Please Login or Register  to view this content.

I ran into this issue before and worked it out. It had to do with the postitioning of the data for the results routine. Any ideas on what I am doing wrong would be great. Thanks again for the help.
Bob

[Andy Pope]

Don't you just need to reset the J variable to 0 before each loop

Please Login or Register  to view this content.

What about this line outside of the worksheet loop, it will only take the last sheet information, is that correct?

Please Login or Register  to view this content.

[JapanDave]

I would change this,

Please Login or Register  to view this content.

to this

Please Login or Register  to view this content.

Then you could transpose the result.

[Qppg]

Department Budget.xlsmThanks for the replies! I have tried both suggestions and still no dice.
This one still blows up in ths same spot

Please Login or Register  to view this content.

This one

Please Login or Register  to view this content.

gives me a different error " This key is already associated with an element of this collection." Run time error 457. Essentially what I am trying to do is go through all the sheets( and now I am realizing based on Andy's question that the code I have will include the Spent YTD sheet and it probably shouldn't seeing as how thats where I want the results to be) gather the data based off str1 value which is the item description. Take those values from each sheet and sum the cost value and count the instances of each unique discription. Maybe attaching my workbook will be helpful. Again thanks for everyone's help. I hope that I am explaining myself well enough.
Bob

[tigeravatar]

Give this code a try:

Please Login or Register  to view this content.

[Qppg]

tigeravatar,
Thank you for your help this works well to go through the sheets and get the data. Is it possible that while looking at the H column in the Months sheets to continue to count the istances like it currently is but not use the cost column (I) as a unique sorter. For example LUB-MC, DRUM 1040.9L, TRIBOL 1100/80W90
Appears 10 times through out the workbook but only twice is the cost the same. Ideally what I would like to do is have it output in 1 row this description with the total number of the times it was seen in the workbook and to sum all of the cost colums so it would look like this.

Please Login or Register  to view this content.

Come to think of it looking at it like this the date colum is kind of irreleavent. Also the qty column would be nice to sum also. Thanks again for everyone's quick responses they have been very helpful.
Bob

[tigeravatar]

Qppg,

You have a lot of data in the months sheet where there is no data in column H (Description). Should that data be ignored?

[Qppg]

tigeravatar,
Where the data is blank in the H Column(Description) if possible use G Column (Vendor). Again thanks for the help.
Bob

[tigeravatar]

Give this code a try. It gathers and outputs data for: Description \ Material \ qty \ Cost \ Count

Please Login or Register  to view this content.

[Qppg]

tigeravatar,
That looks like it does it. I appreciate your help! It is going to take me awhile to digest your code.....but it will be worth it. If you are ever near the Front Range drop me a line. I definitely owe you a beer or two!
Thanks,
Bob