Excel 2016 AutoFilter VBA

[GRCArizona]

Hi -

Just upgraded from Excel 2013 to Excel 2016 and found an issue with a piece of code that worked fine in 2013 but is causing an error in 2016 on the 2nd row ".AutoFilter Field:=h, Criteria1....".

I've got code that filters a list for 2 criteria. The variables work fine as I've tested them and both "h" and "C" are working as expected.

Anybody come across this issue yet?

With ActiveSheet.AutoFilter.Range
     
         .AutoFilter Field:=h, Criteria1:=Sheets(strSheetName).Range("C" & s)
         .AutoFilter Field:=4, Criteria1:="Active"  '''OK
          
          Set rngFiltCol = .Resize(.Rows.Count, 1).Cells.SpecialCells(xlCellTypeVisible)
     
         If rngFiltCol.Cells.Count = 1 Then      '
         Else
            Range("G9:AB" & p).Select '''OK
            Selection.Copy  '''OK
            Sheets(strSheetName).Activate
            Range("G" & Z).Select ''OK
            Selection.PasteSpecial Paste:=xlPasteValues  '''OK
         End If
     End With


[/FONT][/COLOR]

Thanks!

GRC

 

[GRCArizona]

For anyone who's interested, I think I found the cause. Looks like the syntax has changed in Excel 2016. I had to change the first line and got rid of ".Autofilter" and had to include the range.

[COLOR=#000000][FONT=Calibri]With ActiveSheet.Range("$C$8:$AB$4100")[/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri]     [/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri]         .AutoFilter Field:=h, Criteria1:=Sheets(strSheetName).Range("C" & s)[/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri]         .AutoFilter Field:=4, Criteria1:="Active"  '''OK[/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri]          [/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri]          Set rngFiltCol = .Resize(.Rows.Count, 1).Cells.SpecialCells(xlCellTypeVisible)[/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri]     [/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri]         If rngFiltCol.Cells.Count = 1 Then      '[/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri]         Else[/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri]            Range("G9:AB" & p).Select '''OK[/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri]            Selection.Copy  '''OK[/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri]            Sheets(strSheetName).Activate[/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri]            Range("G" & Z).Select ''OK[/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri]            Selection.PasteSpecial Paste:=xlPasteValues  '''OK[/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri]         End If[/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri]     End With[/FONT][/COLOR]