auto fill a product name from a list of product names via a loop

  • 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"

  • This is great, many thanks.

    To add some flexibility to the macro I was thinking of having the range (Set r) be flexible. The list of product changes sometimes on a monthly basis. The list will always start on A1 but can end on any line.

    QUESTION - Can Set r = .[A1:A37] be set up to be variable. I already have a simple countif set up that tells me how many products (i.e. the product count number would be the last line) I have for a given reporting period. This function lets me confirm I have not missed a product report.

    I was thinking of doing an address command that would work off the countif. So using what you showed me in my first question I was going to apply that to defining the range so the loop knows when to stop.

    Am I on the right path??

  • I just ran the code and got an error message that "a" Sub or Function not defined.

    Also, does your coding already know how many lines of products there are by the code

    Set r = .Range("A1", .Cells(Rows.Count, "A").End(xlUp))??

    Here is the actual code I put in. The cell addresses and some of the worksheet names are different from what I showed in the original post. I was trying to keep the original post straight forward, but just in case I have messed up by putting the actual cell and worksheet references in I figured I better show them.

  • When posting code, please paste between code tags. Click the A icon in top right of a reply and then the # icon to insert the tags.

    Let's say that GY15 is the only value in column GY and A20 is the last value in column A. The Address of your 2nd r in post #4 is then: $A$15:$GY$20. Was that your intention? You have to ask yourself what range you want. Most define column A as a required value. So, it will have the most filled rows. Howsoever, if you have a list of say IDs in GY, then just column GY values matter.

    So, maybe you wanted?

    Set r = .Range("GY15", .Cells(Rows.Count, "GY").End(xlUp))
  • I hope I did the code tags right.

    The code you provided seems to get hang up on "a" Sub or Function not defined.

    I thinking I am getting things messed up, let me go back a few steps

    I my actual spreadsheet has a list of products in a worksheet called "cross reference" and the product names are in column GY starting on line 15 and extending down to a line that will change depending on the number of products we have.

    The report is generated in a worksheet called "Attribution Report". I want to paste the names of a product one by one from the list in "cross reference" column GY to "Attribution Report" cell EX20. Once the name had been pasted the report will run automatically.

    Once run, I then want to paste the report into a separate and unique worksheet; however this piece I can do thanks to the help you gave me before.

    So, what I would like to do is have the macro pull in the first name from sheet "cross reference" GY15 and put it in sheet "Attribution Report" in cell EX20, then the macro will save the report in the correct tab and then loop and insert the name of the second product (column GY), again it will put the report in the right tabs and this will keep repeating down the list of products from column GY until it gets to the end.

    I hope this helps explain what I trying to do. Your help is greatly appreciated.

  • Yes, code was fine. Howsoever, you did not use my r to replace your r as I showed you in post #5.

    'Set r = .[GY15:GY45]
    'Set r = .Range("GY15", .Cells(Rows.Count, "A").End(xlUp))
    Set r = .Range("GY15", .Cells(Rows.Count, "GY").End(xlUp))

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!