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

996

New Member
Joined
May 14, 2021
Messages
1
Office Version
  1. 365
Platform
  1. Windows
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!
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,224
Office Version
  1. 2013
Platform
  1. Windows
@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

Well-known Member
Joined
Oct 22, 2012
Messages
1,924
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,132,646
Messages
5,654,567
Members
418,142
Latest member
peterappiahkubi

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top