Move a row to another sheet based on criteria

[cbrown7371]

Hello all!

I am new to VBA and macros and am looking for help.

I have a workbook with multiple sheets within it. I would like to have a row automatically copied over to a sheet based upon a value in the row.

For better understanding, I have a "Master" sheet where I input all of my data then I have other sheets such as "Basketball", "Softball", "Cheer" ect.

When I put the data in a row on the "Master" sheet, I would like for the row to automatically be copied over to the corresponding sheet based on the "Sport" column (column C).

For instance, if I input data into Row 2 on the "Master" sheet and put "Basketball" in cell C3, I would want that row to be copied over to the sheet named "Basketball."

Is this possible? Thanks in advance!

 

[Gokhan Aycan]

Yes, it is possible. You will need to use "Worksheet_Change" event for catching when a change is made to the "Master" sheet. However, how will you put the data in a row on Master sheet? Cell by cell or copy paste from somewhere? Maybe, a Worksheet_BeforeDoubleClick may suit you more and will copy only after double clicking on any cell on the row.

 

[cbrown7371]

Yes, it is possible. You will need to use "Worksheet_Change" event for catching when a change is made to the "Master" sheet. However, how will you put the data in a row on Master sheet? Cell by cell or copy paste from somewhere? Maybe, a Worksheet_BeforeDoubleClick may suit you more and will copy only after double clicking on any cell on the row.

Thanks for the reply!

I will put the data in cell by cell. I tried to upload an image of the workbook and it says the file is too large.

 

[Gokhan Aycan]

2 Methods possible as I mentioned:

Method 1: Change event (which will trigger on every change, but we will run the code only if the last column is changed. This way you will have all info already entered.)

Example: You have 5 columns (A:E). The code will run only when a change is made on any cell on column E. On Master sheet's code page:

Private Sub Worksheet_Change(ByVal Target As Range)
   
    Dim rngCheck As Range
   
    Set rngCheck = Sheets("Master").Columns("E:E")
   
    If Not (Intersect(Target, rngCheck) Is Nothing) Then
   
        ' A change is made on column E
        ' Need cut-paste code here according to activity
        ' Would be good to check if other cells on the row which should be mandatory are also not empty.
   
    End If
   
    Set rngCheck = Nothing
   
End Sub

Method 2: Double Click approach, might not work as expected if you are used to editing cells by double click though.

Again, on Master sheets's code page:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

    ' Check for all mandatory fields
    ' Check for activity
    ' Cut-paste if all looks good

    Cancel = True ' will deactivate the cell edit, so you won't get stuck in it and have to press enter/tab etc

End Sub

You can just select the worksheet and the event from VBA Editor:

Notes:
Target.Row will give you the row number for source (Master sheet).
For activity sheets, you will need to know which row the next copy will be performed. There are a few ways to do that also.
Try to find a reading about Range.CurrentRegion.Rows.Count, or Range.End(xlUp).Row.
If you will be just moving them always, then maybe set rngcheck to just 1 row that you will be using to enter data.