I have this macro which is supposed to copy all yellow-colored cell to Range("A1") in the destination worksheet and I'm using autofilter since there are > 100,000 cells that MIGHT be yellow. I've verified that there are many cells colored yellow and many rows in column B and the colored cells do have the RGB(255, 255, 0) color, but nothing gets copied. WHY?
Sub CopyColoredCellsUsingAutoFilter()
Dim ws As Worksheet
Dim wsDest As Worksheet
Dim rngSource As Range
Dim rngFiltered As Range

' Set your source and destination worksheets
Set ws = ActiveSheet
Set wsDest = Sheet2

' Define the range where colored cells may be
Set rngSource = ws.Range("B1:AW" & ws.Cells(ws.Rows.Count, "B").End(xlUp).Row)

' Apply AutoFilter to the source range based on the yellow color
rngSource.AutoFilter Field:=1, Criteria1:=RGB(255, 255, 0), Operator:=xlFilterCellColor

' Copy the visible (filtered) cells to the destination worksheet
On Error Resume Next
Set rngFiltered = rngSource.SpecialCells(xlCellTypeVisible)
On Error GoTo 0

If Not rngFiltered Is Nothing Then
rngFiltered.Copy Destination:=DestWS.Range("A1")
Else
MsgBox "No colored cells found.", vbExclamation
End If

' Turn off AutoFilter
ws.AutoFilterMode = False

End Sub