I have the following macro that'll take a cell in one worksheet, and if
found, delete the row from a second worksheet.

I want to modify it though, to pull in a 3rd worksheet, and replace the
contents of the source data with the new one. If it's in the
corrections worksheet, I want to replace the entire contents of the
cells in the destination.

How do I do that with this code?


'Criteria range on a different sheet

'Example with the criteria range on a different sheet

'The example below filter C1:C? In a sheet named “DL-Rebate”.
'And use as criteria all the cells in column A of a sheet named "rebate".
'Note: You can use also wildcards like *food* or *store if you want.

Sub Delete_with_Autofilter_More_Criteria()
Dim rng As Range
Dim cell As Range
Dim CriteriaRng As Range
Dim calcmode As Long

With Application
calcmode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

With Sheets("Correct Addresses")
Set CriteriaRng = .Range("A1", .Cells(Rows.Count, "A").End(xlUp))
End With

'Loop through the cells in the Criteria range
For Each cell In CriteriaRng

With Sheets("Direct & Indirect")

'Firstly, remove the AutoFilter
.AutoFilterMode = False

'Apply the filter
.Range("A1:A" & .Rows.Count).AutoFilter Field:=1,
Criteria1:=cell.Value

With .AutoFilter.Range
Set rng = Nothing
On Error Resume Next
Set rng = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
'---If Not rng Is Nothing Then rng.EntireRow.Delete
End With

'Remove the AutoFilter
.AutoFilterMode = False
End With

Next cell

With Application
.ScreenUpdating = True
.Calculation = calcmode
End With

End Sub