Hello Everyone,

This is my first post although I have followed the forum for many years. I've dug around the Internet and have been unsuccessful in finding a macro I can modify to do what I want so I am hoping someone here can help. Attached is the sheet I am working on.

sample.xlsm

What I would like to do is have a macro that based on the active cell, looks upwards and then downwards within the section of the sheet I am working with and then essentially duplicates it by adding to the end of the section.

For example, if F17 happens to be the active cell, the macro would look upwards to identify that B10 is the start of the section and B22 is the end of the section. It would then take that range of rows and insert a copy by adding above B23 effectively duplicating an entire section of the sheet.

Some constants that I can work with:
Every section starts with text in top row of Column B (i.e. B10 says "Option 2"). Directly above this there will always be a blank row (i.e. 9)
Every section ends with a "Subtotal" line (i.e. row 21) followed by a blank row (i.e. 22). The exception is if there is a discount line for a given section such as row 35. Because of this, the end of a section is probably best identified by the first blank row following a subtotal row and then going back up one row.

Hopefully this all makes sense.

-J