Macro to look up and down from the active cell, copying the active "section" of the sheet

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

do you want to copy the entire Option 2 section with the cell formatting and formulas as well or only the function column?

the coding is similar but a bit trickier if you say, select something in column E where there are some blanks at the bottom with corresponding funtions in column F

since, it appears that there will always be a function in column F the code could take this in to consideration even when selecting E17

this code will only copy the values in the function column without formatting

Please Login or Register  to view this content.

this code will copy the entire Option 2 below the original Option 2

Please Login or Register  to view this content.

Thanks much for code examples and replies. Sorry it has taken me some time to get back to this thread. I will digest your suggestions and see if they can work.

Much appreciated.