Quote Originally Posted by rgiglio7489 View Post
very interesting... I just tried yours and it works in your spreadsheet. the only difference between mine and yours is that all of my data is in a table, and some of the values are formulas (but thats covered with LookIn:=xlFormulas). any ideas? I'm still playing around with it... does excel version matter?
The addition of a table to my test did not alter the result. There must be something else going on. Perhaps if you could upload the workbook you are working with.

@jolivanes I agree there are better ways to achieve the desired result, however what you have suggested is not quite what @rgiglio7489 is trying to do. Below is your code with some alterations to do what @rgiglio7489 wants.

Sub With_AutoFilter_Maybe()

Dim wsData As Worksheet
Dim wsComment As Worksheet
Dim lr As Long

'For some reason I could not get the ListObject to work with the object wsData...it would only work with the ActiveSheet object

If ActiveSheet.Name = "Data" Then
    Set wsComment = ThisWorkbook.Worksheets("Comments CMC & NYB")
      Application.ScreenUpdating = False
      With ActiveSheet
        .ListObjects("Table1").Range.AutoFilter Field:=21, Criteria1:="yes"           'change "Table1" to whatever the name of your table is
        lr = .Cells(Rows.Count, 21).End(xlUp).Row
        If lr > 1 Then
            .Range(.Cells(2, 3), .Cells(lr, 3)).SpecialCells(12).Copy Destination:=wsComment.Range("B4")
            .Range(.Cells(2, 16), .Cells(lr, 16)).SpecialCells(12).Copy Destination:=wsComment.Range("D4")
            .Range(.Cells(2, 2), .Cells(lr, 2)).SpecialCells(12).Copy Destination:=wsComment.Range("F4")
            .Range(.Cells(2, 17), .Cells(lr, 17)).SpecialCells(12).Copy Destination:=wsComment.Range("H4")
            .Range(.Cells(2, 7), .Cells(lr, 7)).SpecialCells(12).Copy Destination:=wsComment.Range("J4")
            .Range(.Cells(2, 4), .Cells(lr, 4)).SpecialCells(12).Copy Destination:=wsComment.Range("L4")
            .Range(.Cells(2, 10), .Cells(lr, 10)).SpecialCells(12).Copy Destination:=wsComment.Range("N4")
        End If
        .AutoFilterMode = False
      End With
    Application.ScreenUpdating = True
End If
End Sub