Macro to allow for inserting rows & maintain conditional format ranges

Hello,
I know this problem has been discussed at length before and I've never seen a workaround - at least none that I can remember.
I've been working on this problem for years and last summer I came across a couple of key bits of information.
I been able get a working solution - but it is limited to SINGLE row selected.
I feel pretty confident that what I want to do can be done, but I'm not savvy enough to get it working fully.

The entire concept rests on a Two Key functions:
1> the Insert Rows function leaves the Ranges in the "applies to" part of a conditional format intact by expanding the range!

The inserted rows must be blank - can't be copied

2> using VBA to "paste special Paste:=xlPasteFormats" does NOT include the conditional formatting.

This is the OPPOSITE of paste special "formats" and "Copy & insert copied cells" when done from the user interface.

Macro workflow:
User makes selection of rows (could be full rows or just within a set of columns - this is key if the worksheet is locked because full rows can't be selected).
User runs Macro

(Not present in current macro, but it should check that the selection set is a contiguous set of rows)

Macro creates a selection that = the entire rows of the selected cells.
IF Selection is 'nothing'

Count the number of rows
Use normal 'insert rows' and insert the same number of rows as the user selected

Else

Count the number of rows
Define the entire rows of the user selection as a range to reference, and return to
Insert the same number of rows as the user selection
Use a loop to check every cell of the user range and then copy
There are two steps to the copy function:

If Formula - then copy the formula

Always copy the formats

Final step is to return the user to the original selection or the rows of the original selection which allows the user to run the Macro again on the same set of data

Please Login or Register  to view this content.

You will notice that there is an additional check in the actual code, and that is to check if there is more than 1 row selected, this is because I couldn't figure out to get the copy.cell code to work when there needed to be an offset.
I assume that in the final code, I would not need this code because the structure wouldn't care how many rows were selected.

The first check (If nothing) is purely to keep things moving faster if the cells are blank.

I hope that this not hard to solve, and it would just take someone who knows how to write the syntax to copy a cell and then paste it down by the right number of rows.

Thanks for your help!
This macro really keeps things clean when conditional formats are used.



Regards,

SEPP!

Ok.... I think I solved my own problem...
In which case - Please feel free to use and share!
If anyone is inclined, I'd love to find out if there was a way to speed this up...

Current version of my code:

Please Login or Register  to view this content.

Ok, Update number three.

I have uploaded a file with the macro and an example of one worksheet.
The sheet will pretty much give you nothing but errors because almost every part of it is linked to about 20 other sheets, but you should be able to understand the value of this macro...
As an example, click on an single 'sub-row' (or any single cell in a row) in a area and run the macro - the formulas will copy but the data will not - and the conditional format ranges will GROW! without getting fragmented.
Then Select a range of cells that includes the Area header row & the blank row at the end of that group.
Run the macro and you will see it copy the entire section, again formulas, validation and conditional formats but not the data.

(Compare running the macro to either inserting a blank row or copying and inserting the copied rows... - then check the conditional formats)...

I have this set as a quick access macro button, so running it is click simple!

New features added:

It became apparent today that there was a possible glitch with the way that the Data Validation was copying.
When the data validation was different on each row, the insert row command was setting the data validation to the same for cells that didn't have validation set before (Blank validation rules).
So I had to add a line to make sure that it also got copied correctly.

New additions:
User selection is checked to be contiguous.
User is asked to continue if they select more than one row.
Info box is redesigned for cleaner appearance & added a cancel button.
Data validations are properly copied.
Sheet is locked after code is completed.
There is a beep at the end of the code.

Current 'issues':
1> code finishes with a beep, but user still has to wait to regain interface control.
2> no Undo from Macro Run Command

Regards
SEPP!


Here is the current code:

Please Login or Register  to view this content.