VBA Coding broken code

[jmidd88]

Hi All,

I am very new to this and have been assisted to utilize VBA code for an excel spreadsheet. After a number of months using the code i realized that the intended operation was not happening and instead i started to loose data.

The reason for the code that i have is to move a row of data from one work sheet to another based upon the contents of 2 cells and once all data is collected an "x" will be placed in a 3rd cell to indicate that row is to be "filed" (moved) to the corresponding spreadsheet.

This is being used for technical data in the sports industry to compile a database of current and newly trained officials.

please see the code below:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim xCol As Long
    Dim titleCol As Long
    Dim refSheet As Worksheet
    Dim coachSheet As Worksheet
    Dim presenterSheet As Worksheet
    Dim tgtRow As Long
    
    'Change this to 33 ("AG") for your data
    xCol = 33
    'Change this to 16 ("P") for your data
    titleCol = 16
    
    tgtRow = Target.row
    
    'We don't want to react to changes in row 1, or a value that isn't "x"
    If tgtRow = 1 Or LCase(Target.Value) <> "x" Or Target.Column <> xCol Then
        Exit Sub
    End If
    
    Set refSheet = ThisWorkbook.Worksheets("Previous Referee Data")
    Set coachSheet = ThisWorkbook.Worksheets("Previous Coaching Data")
    Set presenterSheet = ThisWorkbook.Worksheets("Presenter & Ref Coaching data")
    
    With Me
        Application.EnableEvents = False
        .Rows(tgtRow).EntireRow.Cut
        
        Select Case .Cells(tgtRow, titleCol)
            Case "Coach"
                With coachSheet
                    .Rows(.Cells(.Rows.Count, "A").End(xlUp).row + 1).Insert Shift:=xlDown
                End With
            Case "Referee"
                With refSheet
                    .Rows(.Cells(.Rows.Count, "A").End(xlUp).row + 1).Insert Shift:=xlDown
                End With
            Case "Presenter"
                With presenterSheet
                    .Rows(.Cells(.Rows.Count, "A").End(xlUp).row + 1).Insert Shift:=xlDown
                End With
                
        End Select
        
        .Rows(tgtRow).EntireRow.Delete
        
        Application.EnableEvents = True
        Application.CutCopyMode = False
    End With
End Sub

At this stage the problem is when i enter an "x" in to the "AG" column on the corresponding row the data is moved from the Online registration page and not placed anywhere else..... I believe it is being deleted. Any one that may be able to help to look in to this i can send a copy of the spreadsheet.

Your thoughts and assistance would be gratefully appreciated.
(P.s i believe a CRM system would help however we are a not for profit sport and a CRM is in the pipe line... a long way away)