Macro to automate a report

[strider1]

Hi Guys,

Very new to macros. The issue i am facing is that, i have developed a report for around 50 products. the products are in a drop down list. when a product is selected from the list, the report is refreshed with the details about that product.
What i am wanting to do is that, I want to automate the report. I am looking for a macro that would go down the list for all the products and then save the details of each each product in a new workbook, where each product is reprensted by a wroksheet with product name as the worksheet name and then save it as a pdf file.
Any suggestions or code anyone can help me out with?
thank you

 

[Sal Paradise]

It is doable, but what you are asking is too broad and with too few details.

How is the dropdown operated? Is it a data validation list? A "control" dropdown? A "forms" dropdown?

How many products do you have? What is the range of data you want copied?

Basically, the general concept is:

1) Create a new (blank or template-formatted) workbook
2) Create a loop that will go through each item in the menu
3) Each time the menu changes, copy your range of data into the sheet you created in 1)
4) Once the loop has gone through each item, save the new workbook

It's not that complicated, but if you know very little about macros, then it's tough to help, because we don't know where to start.

 

[strider1]

Hi Sal,

thank you for the reply. Dropdown is a data validation list. So which ever product is selected, the dashboard is updated with its information. in total i am looking at 30 products, and i need one workbook with 30 worksheets each with information of one product and worksheet named with the product name.
hope this makes it slightly clearer

 

[Kogersdad]

You could create a pivot table and then from the pivot table, you could create a new spreadsheet for each product which would automatically name each worksheet the applicable product name. All this would be done through basic menu options. This is assuming a pivot table could be created from the drop down list or could replace the drop down list.

 

[Smitty]

Welcome to the Board!

As mentioned, if you can use a Pivot Table, then you'll be in great shape. You can use your Validation List as a Report Filter, then use the Show Report Filter Pages option, which will automatically create a new sheet for each item in the filter.

HTH,

 

[strider1]

Hi,
It is a dashboard report that i have developed. The data is controlled by the product list, which is data validation list and can be controlled by the user. However, to mail the product details, i am required to have dashboard for all individual products in one workbook. So the only way i can think of is to have a macro which would go down the list and generate a worksheet for each product in a new workbook and then save it. Am i makign sense? I was trying to attach the dashboard with this, but cant seem to find any upload option.

 

[Smitty]

If you follow the HTML Maker link in my sig you'll be able to post a shot of your sheet.

 

[strider1]

Hi Smitty,

Please see the link below

http://sdrv.ms/KStnOu

 

[Smitty]

So what range do you want to loop through, and where's the validation list? Is it a named range? If so, what is it?

 

[strider1]

It is a named ranged called "Handsets". how ever in the example i have uploaded, it is from p328.

thanks