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,084,752
Messages
5,379,650
Members
401,619
Latest member
manpais

Some videos you may like

This Week's Hot Topics

  • VBA code giving errors and stopping Excel
    Hello Experts, I have this code being used to loop through files in a file path, and copy specific data to another sheet. It is giving me several...
  • Disable MsgBox message
    Morning, I have a userform where if i leave a ComboBox empty i see a MsgBox warning me that i must enter an invoice number. It is this MsgBox i...
  • Macro Recorder into VBA, Copy Paste Data Filled Cells
    Hi Everyone, I have a macro recorder file that takes a selection of data, copies, then pastes into a new sheet on ("A2:B2") The issue is my...
  • Number format changes while pasting into a cell
    Hi, I am trying to paste a number 180204524303 from an email to an excel cell, however, whenever i try to do so , the the paste value appears as...
  • Collating data
    Hello all. Could someone please help. I am trying to pull all column data from multiple sheets (24 I total so far) into 1 master sheet without...
  • Sum Multiple Columns Based on Multiple Criteria
    I am trying to consolidate data by summing columns G through M based on material, plant, vendor, and fiscal year being identical. The period does...
Top