Copying row into another sheet depending on cell value - having trouble

[996]

First post here - new to VBA. Any help would be greatly appreciated. My goal is simple: I have a "Master" tab with all of my data. If a row contains "1" in column Q, then I'd like that row duplicated on a sheet called "Tier 1," but not deleted from the original "Master." I managed to accomplish this, but have a few questions.

If the data in the "Master" tab is updated and column Q contains another number like 2 or 3, how can I have that reflect / update in the "Tier 1" sheet without re-running the code? Every time I re-run the code, it recreates all of the data again and essentially makes a copy of it below the existing data in the "Tier 1" tab. It updates correctly, but just copy/pastes right below the original. My current code is below:

Sub MoveRowBasedOnCellValue()
'Updated by Extendoffice 2017/11/10
Dim xRg As Range
Dim xCell As Range
Dim I As Long
Dim J As Long
Dim K As Long
I = Worksheets("Master").UsedRange.Rows.Count
J = Worksheets("Tier 1").UsedRange.Rows.Count
If J = 1 Then
If Application.WorksheetFunction.CountA(Worksheets("Tier 1").UsedRange) = 0 Then J = 0
End If
Set xRg = Worksheets("Master").Range("Q1:Q" & I)
On Error Resume Next
Application.ScreenUpdating = False
For K = 1 To xRg.Count
If CStr(xRg(K).Value) = "1" Then
xRg(K).EntireRow.Copy Destination:=Worksheets("Tier 1").Range("A" & J + 1)
J = J + 1
End If
Next
Application.ScreenUpdating = True
End Sub


I would just like a row to be removed from "Tier 1" if the data on the "Master" tab changes to a value other than "1" / would like "Tier 1" to refresh and add a row if a value in column Q on the "Master" tab changes from any other number to "1"

Hope I explained it well!

 

[Snakehips]

@996 Welcome to MrExcel.
My first question would be, is the data in Master Q being updated by manual entry?
If not then, you will struggle to get your updates happening automatically and will have to 'run' your code to check / make updates.
Can you give an idea of the volume of data?

If column Q in Master changes from anything o 1 then that row is to be copied to Tier1 ?
If column Q in Master changes from 1to anything then that row is deleted from Tier1 ?

 

[maabadi]

AND Other Questions:
1. if you change one value from 1 to other, And we have more than one row with 1 value , How we know which row should be deleted.
? which column can be criteria for find specific row for deleting or not duplicating?