Possible to have macro add new partial row with a specific column range on any clicked row

Hi,

I've currently been using a button to run the macro below to add an entire new row that keeps formulas and clears out entered data by selecting any cell and hitting the button. I'm working on another file now that will have data in columns A,B,C, D, and E that can't be changed or move down (data has to stay in it's current cells) so the current macro won't work as it moves the entire row down which would break the data in those columns.

Starting in cell F5 data will be added through AF5 and continue down each row indefinitely. I've been trying to modify this macro (with of course no luck) to have it do the same add new row function when selecting any cell from F5 and down but only add a partial row starting at column F and going across not effecting the information in columns A,B,C, D, and E.

Another aspect that is needed with the add row macro is for the new row to be inserted at the same place on 5 out of 10 sheets in the workbook. The current macro will add the row to multiple sheets if I group the sheets prior to running the macro, but I need the rows added to the different sheets without having to manually group the sheets first.

Hope I've explained it clear enough. Not sure if this is even possible, but if it is help pointing me in the right direction for a solution would be greatly appreciated... I've come up empty so far.

Please Login or Register  to view this content.

Thank you!

AlphaFrog thank you! That works great when the sheets are unprotected, but I forgot to say they are protected (all with the same password).

I was using the following code in ThisWorkbook module...

Please Login or Register  to view this content.

When i run the macro with the sheets protected it gives a debug error here....

image.jpg


Any ideas on how to or the best way to run it with the sheets protected?


Also I have another button to delete a row, that only deletes the one selected row but need it to delete the same row on the 5 sheets now. Hacking up your add row macro code I came up with below, it deletes the row on the first sheet then gives a run-time error '9' Subscript out of range.... not sure where to go from there?

Please Login or Register  to view this content.

Thank you AlphaFrog! haha yes add didn't make sense, it should have been delete I missed changing it.

Both macros work almost perfectly... but for some reason the both stop on sheet4 once the macro has run correctly through sheet 5 and do not return to the original sheet and cell.

The add row macro did return to the original sheet/cell before the protection code was added. Do I need to add something extra now with the protection to end on the original sheet and starting cell?

Thank you again for all your help.

Figured it out. Thanks again AlphaFrog!