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

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

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] [/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.

Sub CopyAndInsertRow()
ActiveSheet.Unprotect
 
 R = ActiveCell.Row
 LC = Selection.SpecialCells(xlCellTypeLastCell).Column
 
    With Range(Cells(R, 1), Cells(R, LC))
        .Copy
        .Insert Shift:=xlDown
    End With
    
    On Error Resume Next
    With Range(Cells(R + 1, 1), Cells(R + 1, LC))
    .SpecialCells(xlCellTypeConstants).Clear
    End With
    On Error GoTo 0

        
    Application.CutCopyMode = False
    ActiveSheet.Protect , AllowFiltering:=True, AllowFormattingColumns:=True, AllowFormattingRows:=True

End Sub

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

Sub CopyAndInsertRow()
ActiveSheet.Unprotect
 
 R = ActiveCell.Row
 LC = Selection.SpecialCells(xlCellTypeLastCell).Column
 
 O = 1
 
    With Range(Cells(R, 1), Cells(R, LC))
        
10 If Rows(R + O).RowHeight = 0 Then O = O + 1: GoTo 10

        .Copy
    Range(Cells(R + O, 1), Cells(R + O, LC)).Insert Shift:=xlDown
    End With
    
    On Error Resume Next
    With Range(Cells(R + O, 1), Cells(R + O, LC))
    .SpecialCells(xlCellTypeConstants).Clear
    End With
    On Error GoTo 0

        
    Application.CutCopyMode = False
    ActiveSheet.Protect , AllowFiltering:=True, AllowFormattingColumns:=True, AllowFormattingRows:=True

End Sub