PDA

View Full Version : Help with a macro



jchula
02-15-2013, 03:08 AM
Hi,
I´m new on Forum and sorry about my English.
I want to build a macro but my knowledge's of VBA are limited.
I have a workbook with various worksheets.
This worksheets represents stores with month sellout products (this products and stores changes month by month)
I want to create a report like that:

for example i want to know de sellout product of January

column with code products store1 store2 ... as many as worksheets
11111 1 0 5 ...
22222 3 1 3 ...
33333 2 4 1 ...
....

I attached the example file.
thanks for you help!!!

p45cal
02-15-2013, 01:13 PM
I'm going to give you a flexible solution which will allow you to get a whole range of summaries, using a pivot table. A pivot table likes to see a database-type structure as its source data.
So in the attached is a macro (consolid) which deletes, then re-creates, a sheet called allstores2, sets that data to be the source data for the pivot table, then refreshes the pivot table. This sheet is the source data for the pivot table on the report sheet. (By the way, the data you have in your sheets looks like it might have come from a pivot table or other database-reporting tool; if you have access to its source data that would be much cleaner.)

Not essential, but will allow more freedom of choice of reports, you should convert the dates at the top of each store sheet to be real dates, not just strings which look like dates. I've done this on the attached.
The pivot table now gives you a large range of reports on that data.

Note that to be able to attach the file it had to be less than 1MB so I deleted both the allstores2 and the all stores sheets which I didn't use. Press the Button 2 on the report sheet to re-create the allstores2 sheet.

jchula
02-15-2013, 04:41 PM
THANKS for your post but my initial idea was search in every store in particularly month some products with a specific cpr.
In the future i will change the stores worksheets by another stores and other products but i want the same report.
My idea was build a macro that uses a function like a vlookup function.
I will change stores worksheets constantly, so with the excel vlookup function in the excel i have to change the parameters of the function when i make a change.
So my idea was build a macro to search my products in the specific month on every store on my workbook.
Can you help me with that?
Thanks again

p45cal
02-15-2013, 05:08 PM
Changing worksheets is exactly what this is good at.
At the moment, the consolid function decides what sheets to use by looking at each sheet's name, specifically that it begins with stor. It doesn't have to be that, it can be almost anything about the sheet; a particular cell might have something in it which qualifies it as a sheet to include in the consolidation. Your code would have to do just the same anyway.

Regarding searching for a specific product in a specific month, again, that's what the pivot table is very good at; choose the month and the product using the dropdowns in the pivot table and you have your answer. Want several products? Choose several. Want several months? Choose several months too. Don't want to include all stores, choose only the stores you want results from.

I don't see that writing some very specific code is worthwhile - especially as once it's written, you'll probably want to change it here and there to tweak the results - right now, you can get the results you want, tweaked or not, fairly robustly.