Deleting Rows In a Table Based on Data From Another Worksheet

fishymuffin

New Member
Joined
Jan 18, 2022
Messages
1
Office Version
  1. 365
Platform
  1. Windows
I have a file with three worksheets.
Sheet1 is a summary of all clients.
Sheet2 has a table that copies only clients with "active" status from Sheet1, Sheet3 has a table that copies only clients with "buyer" status from Sheet1.

Is it possible to write a vba code that will delete rows from Sheet2's table if their status in Sheet1 changed from "active" to any other status?

Here is the code that copies the data from Sheet1 to Sheet2 (it's the same for Sheet3, just with different table and sheet names):

VBA Code:
Sub CopyActiveRecords()

Dim StatusCol As Range
Dim Status As Range
Dim PasteCell As Range

Set StatusCol = Sheet1.Range("A2:I150")

For Each Status In StatusCol

    If Sheet2.Range("A2") = "" Then
        Set PasteCell = Sheet2.Range("A2")
    Else
        Set PasteCell = Sheet2.Range("A1").End(xlDown).Offset(1, 0)
    End If
    If Status = "active" Then Status.EntireRow.Copy PasteCell
    
Next Status

With ActiveSheet
       Set Rng = Range("A1", Range("B1").End(xlDown))
       Rng.RemoveDuplicates Columns:=Array(1, 2), Header:=xlYes
End With


End Sub

Alternatively, a code to "refresh" the status column in Sheet2 to show the new status from Sheet1 is also okay, however less ideal.
Thanks!
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
i don't know the layout, but if you filter on the status "active", then you insert as many rows in sheet2 just below your headerrow, you copy those filtered records to there and then you remove duplicates.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

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
Back
Top