BENSON
07-01-2009, 12:08 AM
I thought I knew this one but apparently not .If the work sheet "TS SP" refered to in the code below is protected, where would I need to insert the code "Work Sheet unprotect" are some other code to unprotect the work sheet "TS SP"
THANKS
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B:B" '<== change to suit
Dim RowNum As Long
On Error GoTo ws_exit
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
RowNum = Application.Match(.Value, Worksheets("TS sp").Columns(1), 0)
Worksheets("TS sp").Rows(RowNum).SpecialCells(xlCellTypeConstants).ClearContents
Worksheets("TS SP").Rows(RowNum).Cut
Worksheets("TS SP").Rows(38).Insert Shift:=xlDown
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub
THANKS
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B:B" '<== change to suit
Dim RowNum As Long
On Error GoTo ws_exit
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
RowNum = Application.Match(.Value, Worksheets("TS sp").Columns(1), 0)
Worksheets("TS sp").Rows(RowNum).SpecialCells(xlCellTypeConstants).ClearContents
Worksheets("TS SP").Rows(RowNum).Cut
Worksheets("TS SP").Rows(38).Insert Shift:=xlDown
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub