VBA Coding broken code

jmidd88

New Member
Joined
Mar 17, 2014
Messages
1
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:
Code:
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)
 
Last edited:

Forum statistics

Threads
1,081,901
Messages
5,361,937
Members
400,666
Latest member
UDLearning

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top