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
Sub CopyAndInsertRow()
ActiveSheet.Unprotect
Dim rw As Long
With Selection
rw = .Row
.EntireRow.Copy
.Insert Shift:=xlDown
On Error Resume Next
Cells(rw + 1, 1).EntireRow.SpecialCells(xlCellTypeConstants).ClearContents
On Error GoTo 0
Application.CutCopyMode = False
ActiveSheet.Protect , AllowFiltering:=True, AllowFormattingColumns:=True, AllowFormattingRows:=True
End With
End Sub
Bookmarks