Remove duplicates in all sheets simultaneously through vba-CodePudding

I have this code that I used back then for the removal of duplicates in all sheets. When I used it on my current workbook, it suddenly returned with a run-time error '1004' pointing to the range that I defined. What I want to do is to remove all duplicates based off their value in column F. Can somebody please help me revise the code? Also, would it be possible to revise it so that will ignore specific named sheets? Thanks!

Sub RemoveDuplicates()
     Dim Current As Worksheet
     Dim starting_ws As Worksheet
     Set starting_ws = ActiveSheet

     For Each Current In Worksheets
        Current.Activate
        
        ActiveSheet.Range("$A$1:$Q$1000").RemoveDuplicates Columns:=6, Header:=xlYes

     Next
     
starting_ws.Activate

End Sub

CodePudding user response：

To exclude named sheets:

Sub RemoveDuplicates()

     'declare variables
     Dim Current As Worksheet, lastrow as Long

     'loop through all worksheets using 'current' as the label 
     For Each Current In Worksheets

        'find row number of bottom of column F
        lastrow = Current.Cells(Current.Rows.Count, "F").End(xlUp).Row

        'if current's tab name isn't one of these...
        If Current.Name <> "IgnoreThisTab" And Current.Name <> "IgnoreThatTab" Then

           ' then remove duplicates
           Current.Range("$A$1:$Q$" & lastrow).RemoveDuplicates Columns:=6, Header:=xlYes

        ' and that's it
        End If

     'end of loop
     Next

End Sub