Duplicating Rows with Multiple Selection Drop-Downs

asked 2022-06-03T12:47:43.377+00:00
Mize, Laura 1 Reputation point

I need to create a spreadsheet that has 20 duplicate rows, each containing 8 cells with drop-down menus that allow the user to select multiple options within each drop-down. I tried copy and pasting and while the drop-down remains intact, the option to select multiple options goes away (it only lets you select one item in each drop-down). Is the only way to change that via code (example: adding "= "$B$2-10" Or Target.Address" every cell in the sheet (which means adding 160 of them to the code)? I don't code - I watched a video and know how to just do that within that code. I will need to do this type of thing frequently and would love to find an easier way than adding so many separate entries to the code.

Thanks!

Excel Management
Excel Management
Excel: A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.Management: The act or process of organizing, handling, directing or controlling something.
1,334 questions

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. answered 2022-06-06T08:13:08.733+00:00
    Emily Hua-MSFT 21,601 Reputation points Microsoft Employee

    Hi @Mize, Laura

    According to your description, your request needs the hlep of VBA code.
    So I will add the tag of VBA. Thanks for your understandings.

    > Is the only way to change that via code (example: adding "= "$B$2-10" Or Target.Address" every cell in the sheet (which means adding 160 of them to the code)?

    I personally suggest you replace Target.Address with Target.Column . For more, please refer to this link.

    If the cells that include drop-down list is from G column to N column, then you can try Target.Column > 6 And Target.Column < 15 to restrict the range.
    Then you can apply the code to cells that include drop-down list at one time instead of to one cell.

    You can also try the way that uses If Not Intersect(Target, Columns(3)) Is Nothing Then ··· in the link above.

    Hope the information could be helpful.

    If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.