[RESOLVED] Ecxcel vexing autofilter problem

[MartinLiss]

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

[OptionBase1]

The first thing to try is to disable the error handling you have in place and see what, if any, errors are being generated but suppressed. That may reveal the underlying issue.

[MartinLiss]

I've found a completely different way to do this.

[jdc2000]

Note the "Filter on Color" in Excel is actually broken. It will not always show ALL of the cells with a certain color when you filter on a color. Sometimes, it will not even show the color you want to filter on. To work around this, I generally have a column that I can use to filter on something else (cell contents) that WILL always work.