CODEWITHSUNDEEP
excelvba
B-Rye
B-Rye

Reputation: 72

This selection isn't valid. Make sure the copy and paste areas don't overlap

Keep getting error mentioned in the title on the "insert" line of code. Both the cut and insert lines of code appear to be the same size. I've been staring at this thing for hours. I can't figure out where I'm messing up.

Sub Worksheet_Change(ByVal Target As Range)
'convert communites by status

If Not Intersect(Target, Range("H1:H1000")) Is Nothing Then
If Cells(Target.Row, 8) = "Takedown" Then
Range(Target.EntireRow, Target.Offset(13, 0).EntireRow).Cut
Sheets("AIKEN.AUGUSTA-TAKEDOWN").Range(Range("A12").EntireRow, 
 Range("A25").EntireRow).Insert
 Range("B12:B25").Interior.ColorIndex = 3
 Range("C13").Select

End If
End If

End Sub

expected result: row range is cut from one part of the sheet and inserted in a different area of the sheet.

Actual result: error on insert line of code.

Upvotes: 1

Views: 10202

Answers (1)

Tim Williams
Tim Williams

Reputation: 166156

Try this:

Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range

    If Target.CountLarge > 1 Then Exit Sub 
    Set rng = Application.Intersect(Target, Me.Range("H26:H1000"))

    If Not rng Is Nothing Then            
        If Cells(rng.Row, 8) = "Takedown" Then
            Application.EnableEvents = False '<< don't re-trigger on Cut
            Range(rng.EntireRow, rng.Offset(13, 0).EntireRow).Cut
            Me.Range("A12:A25").EntireRow.Insert
            Application.EnableEvents = True  '<< re-enable events
            Me.Range("B12:B25").Interior.ColorIndex = 3
            Me.Range("C13").Select
        End If
    End If

End Sub

Upvotes: 1

Related Questions