Hello,
I got the solution from this forum to remove duplicate in child sheet if the records for columns A, H and S are exactly matching with the columns A, H and S of Master sheet.
When I used it in some of my other process, it is not working. can anyone help me.
Advance thanks
Option Explicit
Sub RemoveMatchesToMasterOnManySheets()
Dim LR As Long, ws As Worksheet
With Sheets("master")
LR = .Range("A" & .Rows.Count).End(xlUp).Row
.Range("AI2:AI" & LR).FormulaR1C1 = "=RC2&""-""&RC8&""-""&RC19"
End With
For Each ws In Worksheets
If ws.Name <> "master" And ws.Range("A1").Value = "Responsibility" Then
With ws
.AutoFilterMode = False
.Range("AI1") = "key"
LR = .Range("A" & .Rows.Count).End(xlUp).Row
.Range("AI2:AI" & LR).FormulaR1C1 = "=ISNUMBER(MATCH(RC2&""-""&RC8&""-""&RC19, master!C10, 0))"
.Range("AI:AI").AutoFilter Field:=1, Criteria1:="TRUE"
LR = .Range("A" & .Rows.Count).End(xlUp).Row
If LR > 1 Then .Range("A2:A" & LR).EntireRow.Delete xlShiftUp
.AutoFilterMode = False
.Range("AI:AI").ClearContents
.Range("A1").AutoFilter
End With
End If
Next ws
Sheets("master").Range("AI:AI").ClearContents
End Sub
Bookmarks