Syntax Error

[Damian37]

Hello All,
After creating code to copy my filtered data within my raw data tab, I realized there is a value coming back that I would like to exclude. I'm currently filtering on "High", "Moderate-High". HOwever I'm also retrieving the value "Moderate" alone. I'm trying to exclude it with the following code, but I'm receiving a syntax error. I've tried moving the "Criteria2" portion to different areas, but nothing has worked so far.

Sub FilterMultipleCriteria()
'
 '   On Error Resume Next
    
' Filter by Multiple Criteria

 Application.DisplayAlerts = False
  On Error Resume Next
  Sheets("Sheet1").Delete
  On Error GoTo 0
  Sheets.Add After:=Sheets(Sheets.Count)
  ActiveSheet.Name = "Sheet1"


    Dim ws As Worksheet
    Dim FilterRange As Range
    Dim criteriaArray As Variant
    Dim CopyRange As Range
    Dim DestRange As Range
    Dim Cell As Range
    
    Set ws = ThisWorkbook.Sheets("Report Data")
    Set FilterRange = ws.Range("E1:EF" & ws.Cells(ws.Rows.Count, "E").End(xlUp).Row)
    Set CopyRange = FilterRange.SpecialCells(xlCellTypeVisible)
    Set DestRange = Sheets("Sheet1").Range("A1")
    
        criteriaArray = Array("High", "Moderate-High")
    
    With ws
        .AutoFilterMode = False
        
        FilterRange.AutoFilter Field:=1, Criteria1:=criteriaArray,  _
        Operator:=xlFilterValues,
        Criteria2:="<>*Moderate*"
        
        
        
    End With
    
CopyRange.Copy DestRange

End Sub

Any and all help is greatly appreciated. Thanks!

D

 

[Damian37]

Right from the word go we've only been copying the columns from E onwards, I even asked the question in post #14

to which you replied:

the "filtered data" has always been from column E.
I'll change the code to copy all columns from A to AO. Leave it with me.

Hi Kevin,
I didn't realize that's what was going on. I've updated the code, and everything is working correctly now. Thank you so much for all of your help! You really got me out of a jam!

 

[kevin9999]

Glad we got there in the end
If you're interested, the code could actually be even simpler than before. Here's my final suggestion, using this file to test: All Items- CAPA Tracker Fields Generic shareable - Marcro Unifinished Ver 4.xlsm

Option Explicit
Sub FilterMultipleCriteria_V4()
    Dim ws1 As Worksheet, ws2 As Worksheet, criteriaArray
    
    'Set the data range & criteria
    Set ws1 = Worksheets("Report Data")
    Set ws2 = Worksheets("Filtered Data")
    criteriaArray = Array("High", "Moderate-High")
    
    'Filter & copy
    If ws1.AutoFilterMode Then ws1.AutoFilterMode = False
    With ws1.Range("A1").CurrentRegion
        .AutoFilter 5, criteriaArray, 7
        If .SpecialCells(xlCellTypeVisible).Address <> .Rows(1).Address Then
            ws2.Range("A1").CurrentRegion.Offset(1).ClearContents
            .Offset(1).Resize(.Rows.Count - 1).Copy ws2.Range("A2")
        End If
    End With
    ws1.AutoFilter.ShowAllData
    
End Sub

 

[Damian37]

Glad we got there in the end
If you're interested, the code could actually be even simpler than before. Here's my final suggestion, using this file to test: All Items- CAPA Tracker Fields Generic shareable - Marcro Unifinished Ver 4.xlsm

Option Explicit
Sub FilterMultipleCriteria_V4()
    Dim ws1 As Worksheet, ws2 As Worksheet, criteriaArray
   
    'Set the data range & criteria
    Set ws1 = Worksheets("Report Data")
    Set ws2 = Worksheets("Filtered Data")
    criteriaArray = Array("High", "Moderate-High")
   
    'Filter & copy
    If ws1.AutoFilterMode Then ws1.AutoFilterMode = False
    With ws1.Range("A1").CurrentRegion
        .AutoFilter 5, criteriaArray, 7
        If .SpecialCells(xlCellTypeVisible).Address <> .Rows(1).Address Then
            ws2.Range("A1").CurrentRegion.Offset(1).ClearContents
            .Offset(1).Resize(.Rows.Count - 1).Copy ws2.Range("A2")
        End If
    End With
    ws1.AutoFilter.ShowAllData
   
End Sub

Thank you very much. Question:
I've added four new columns at the end of my filtered data, and I believe the line is clearing the existing data within the "Filtered Data" tab. Is there a way I can have the macro run, data pasted, and not clear the formulas I've written in the four new columns?

ws2.Range("A1").CurrentRegion.Offset(1).ClearContents

 

[kevin9999]

Replace that line with:

ws2.Range("A1").CurrentRegion.Offset(1).Resize(, 41).ClearContents

 

[Damian37]

Replace that line with:

ws2.Range("A1").CurrentRegion.Offset(1).Resize(, 41).ClearContents

That did it. I cannot thank you enough!!!!!! Happy holidays!!!!