Script to insert Row

[Jeremy]

How would I write a scrtip to insert a row under the number in A that holds a
"NS" or "MT"? I would also like it to copy the row that has "NS" or "MT" in
it and paste it in the row that was inserted underneith. When it copies the
row it will copy the whole row except what is in A.

Example Before
A B
1 MT 12
2 D 14
3 x 15


Example After
1 2
1 MT 12
2 12
3 D 14
4 X 15


Thanks

 

[Luke M]

Sub CopyInsert()
'Find the last cell in column A
With ActiveSheet
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
For i = LastRow To 1 Step -1
If Cells(i, 1).Value = "NS" Or Cells(i, 1).Value = "MT" Then
'Copy row
Cells(i, 1).EntireRow.Copy
'Insert new row
Cells(i + 1, 1).Insert Shift:=xlDown
'Clear the inserted row's A cell
Cells(i + 1, 1).ClearContents
End If
Next

End Sub

 

[Mike H]

Jeremy

try this

Sub Insert_Copy()
Set Sht = Sheets("Sheet1") 'Change to suit
Dim x As Long
MyColumn = "A"
For x = Sht.Cells(Rows.Count, MyColumn).End(xlUp).Row To 1 Step -1
If UCase(Sht.Cells(x, MyColumn)) = "MT" _
Or UCase(Sht.Cells(x, MyColumn)) = "MS" Then
Sht.Rows(x + 1).insert
Sht.Rows(x).Copy Destination:=Cells(x + 1, 1)
Sht.Cells(x + 1, 1).ClearContents
End If
Next x
End Sub

--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.