VBA Import .xlsx and Search for criteria, sum into new workbook

Hello Everyone,

I've been searching through posts for a while now and taking bits and pieces from people's existing code and trying them out with no success. I'm attempting to do something somewhat simple, but its just tedious and takes me roughly 30 minutes to do it by hand every day.

The pizza shop I manage puts out weekly consolidated summaries of the amount of products we sell. The only problem is that it is too specific. The owners want a simple report every day that consists of general items like "Large Hand-tossed Pizzas" sold, or "2 Liter Sodas" sold. The report the computer generates will spit out a 350 row .xlsx sheet that contains rows that say "Drinks -- Cherry Pop 2L Bottle" or "Pizza -- 12" Hand-tossed Deluxe" with the quantity a few columns to the right. There's probably 40 different variations of a 12" Hand-tossed pizza, so it creates a row for each one. I just need to know how many "12" Hand-tossed pizzas" in general.

Any help would be so much appreciated. Here's what I would like to see:

• 1) Upon opening a master Weekly.xltm sheet (which is also basically the template), a macro automatically asks the user to import the .xlsx sheet containing the data to be summarized (basically a "browse..." dialog).
• 2) The macro creates a workbook that uses the master file as a template for the information we're summarizing.
• 3) The macro searches through the imported sheet, finding rows that contain "Drinks" in column B, AND the text "20-oz" in column D, and takes the quantity number from column K, and summarizes it into its corresponding cell in a temporary sheet (column B).
• -It does this for all items listed in the template
• 4) It also searches for "Pizza" in column B, the phrase "12"" in column D, and the phrase "Hand-tossed" in column J. If it finds any rows with those 3 criteria, it sums it into the corresponding cell in column B in the Weekly.xltx "Medium Hand-tossed" row. [medium = 12" in this case]
• 5) after running, it gives the save as... dialog asking the user where to save it the temporary sheet with the information.

I've created an example.xlsx file that looks pretty much what the system automatically generates, I've just taken out all but 20 rows.
The Weekly.xlsx file is the template I'd like the master file macro file to use, and have it automatically generate this information with a few clicks. So the template macro file shouldn't be modified itself, it should import a file, run the calculations, and save those to a new file, leaving the original macro file / template unmodified.

If someone can write the code to do these 4 steps, I can likely see what's going on and expand it to fill out the other 40 lines of information I redacted from the weekly.xlsx for this purpose. I would be forever grateful if this was accomplish-able. Ideally, a person with limited abilities computer skills would be able to open this file and select the summary file for the week and it all it does after that is ask the user where to save the new sheet.

Thank you very much for your help.

Thank you AlphaFrog. This worked marvelously.

The only thing I changed was the save as sequence. I was having issues with it not saving, and excel crashing, so I replaced

Please Login or Register  to view this content.

with this

Please Login or Register  to view this content.

I'm still working on touching that up so that the program opens up the newly saved file after closing the main macro file, but the script works none-the-less.

Thanks again