Copying Selected Row & Formulas and inserting new row below with formulas only

Hello All,

My goal that I am trying to achieve is to have a user be able to select a row that contains data within a workseet- hit a macro, and a row will be inserted below the selection. Within this insert I wish the formulas that are contained within the selected row are copied into the new row, but no values. So users are able to manually enter values throughout the protected worksheet, but those values should not be copied line to line.

So I attempted this with the following VBA- but the problem I am having is, if I hide any rows throughout the worksheet, it seems to completely bug out / delete rows, and does not work properly- and I am not sure why. If I modify the filter through the drop down, it also seems to make the macro act funny. It only seems to work 'somewhat' correctly if I never change the filters/or hide any rows (out of 1000)

Any help would be appreciated!
Mike

Please Login or Register  to view this content.

Welcome to the Forum.

Unfortunately you will not get many if any responses because you have not inserted code tags.

Unfortunately the moderators and administrators are very strict on this.




Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Click on Edit to open your thread, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

Ok this code works.

If the next row is hidden it messes up, I suppose this depends on your point of view.

So if you select Row 5 and Row 6 is hidden
Then a new row 6 is created. The new row 6 is hidden,
the original row 6 becomes row 7 and is not hidden.

Please Login or Register  to view this content.

This Version inserts the copied row at row 7, below the hidden row.

Please Login or Register  to view this content.