I run the same monthly report for a number of different products
I have a list of the product names
currently I have a drop down that puts the product name in a given cell
once in that cell a report is generated
I then copy and paste the report to an individual worksheet for that particular product for the respective month
I go back to the drop down menu, pick the next product name in the list and repeat the process.
I already know how (via a macro) to direct the reports to the individual worksheets
I wrote a macro that does this but it has the same coding repeated for each separate product; we have a lot of products and it keeps growing (or shrinking). Every time we add or remove a product I have to add to or delete some of the coding.
it seems to me I should be able to reduce this to about 6 lines instead of 6 line repeated dozens of time with the only difference being the product name that I have hard coded.
QUESTION - how can I write a macro loop so I can just go through the list of product names automatically.
lets say the product names are a tab called "Cross Reference" and are listed in cells A1 to A37 and to run the report I have to put the product name in cell T10 in the tab called "Report"