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]

OK, please try the following on a copy of your workbook. Please do not change, add to or delete any part of the code before you try it.

Option Explicit
Sub FilterMultipleCriteria()
    Dim ws As Worksheet, r As Range, criteriaArray

    'Add the new Sheet1
    Application.DisplayAlerts = False
    On Error Resume Next
    Sheets("Sheet1").Delete
    On Error GoTo 0
    Application.DisplayAlerts = True
    Sheets.Add After:=Sheets(Sheets.Count)
    ActiveSheet.Name = "Sheet1"
   
    'Set the data range & criteria
    Set ws = Worksheets("Report Data")
    Set r = ws.Range("E1:AO" & ws.Cells(Rows.Count, "E").End(xlUp).Row)
    criteriaArray = Array("High", "Moderate-High")
   
    'Filter & copy
    If ws.AutoFilterMode Then ws.AutoFilter.ShowAllData
    With r
        .AutoFilter 1, criteriaArray, 7
        If .SpecialCells(xlCellTypeVisible).Address <> .Rows(1).Address Then
            .Copy Worksheets("Sheet1").Range("A1")
        End If
    End With
    Worksheets("Sheet1").Columns("A:B").EntireColumn.AutoFit
    ws.AutoFilter.ShowAllData
   
End Sub

Works like a charm Kevin. So, getting back to the bonus.
My thinking is instead of creating a new sheet with my code every time I run the macro to filter the data, I would like to make the Workbook a template where I can simply copy new raw data from a new exported report, and then paste that data within the "Report Data" tab in the "Macro-Template" file. This way, the copied data will be pasted up to column AO, and then I will have added new information within columns AP... that are calculated columns based on the new data. I'm basically going to finish the project by using a bunch of if then statements to further filter the filtered data. Does that make a little more sense?

 

[kevin9999]

and then paste that data within the "Report Data" tab

Where exactly in the Report Data sheet would you want it pasted?

 

[Damian37]

Where exactly in the Report Data sheet would you want it pasted?

That part would be manual. I would be exporting a report from a webserver and so all of the columns would remain the same and I would be pasting the new information starting in cell A1 just like it is now. The only difference is I would have two sheets added to the report. 1 sheet would have the filtered data in it. So just like now when the filtered data is pasted into Sheet1, instead of creating a new sheet each time, I can rename the sheet to something like "Filtered Data" and then set the paste destination to that sheet name. Since I would be removing any columns, the filtered data will still go to column AO. In the "Filtered Data" sheet I would add columns starting at AP1 where the next cell AP2 will have an excel formula that will copy the important dates and paste them into column AP, another column (AQ1) that will copy the status (closed-verified, in-progress, open), another column that will determine if the item is overdue or not, and so on and so forth.

 

[kevin9999]

Does that mean that you want to overwrite the existing data in the "Filtered Data" sheet each time?

 

[Damian37]

Does that mean that you want to overwrite the existing data in the "Filtered Data" sheet each time?

Yes, this way the new columns I create will update as I run the macro and place new filtered data into the "Filtered Data" tab.

 

[kevin9999]

OK, can I suggest the formula is added by the macro as well, because the row numbers will (probably) vary?

 

[Damian37]

OK, can I suggest the formula is added by the macro as well, because the row numbers will (probably) vary?

That would be great, but I didn't want to make it more complicated by adding it to the code. Only because I'm not entirely sure what the formulas are going to be at this moment, I wanted to use the filtered data we have now and create the excel formulas into new columns so I can have it tested before adding it to the code. Make sense?

 

[kevin9999]

OK, the code actually becomes a bit simpler. Please try the following. Important note: it is necessary that your existing file already has a sheet called "Filtered Data", with headers, already before the code is run. I therefore suggest you download this file: All Items- CAPA Tracker Fields Generic shareable - Marcro Unifinished Ver 3.xlsm in order to test the code.

Option Explicit
Sub FilterMultipleCriteria_V2()
    Dim ws1 As Worksheet, ws2 As Worksheet, r As Range, criteriaArray
    
    'Set the data range & criteria
    Set ws1 = Worksheets("Report Data")
    Set r = ws1.Range("E1:AO" & ws1.Cells(Rows.Count, "E").End(xlUp).Row)
    Set ws2 = Worksheets("Filtered Data")
    criteriaArray = Array("High", "Moderate-High")
    
    'Filter & copy
    If ws1.AutoFilterMode Then ws1.AutoFilter.ShowAllData
    With r
        .AutoFilter 1, 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]

OK, the code actually becomes a bit simpler. Please try the following. Important note: it is necessary that your existing file already has a sheet called "Filtered Data", with headers, already before the code is run. I therefore suggest you download this file: All Items- CAPA Tracker Fields Generic shareable - Marcro Unifinished Ver 3.xlsm in order to test the code.

Option Explicit
Sub FilterMultipleCriteria_V2()
    Dim ws1 As Worksheet, ws2 As Worksheet, r As Range, criteriaArray
  
    'Set the data range & criteria
    Set ws1 = Worksheets("Report Data")
    Set r = ws1.Range("E1:AO" & ws1.Cells(Rows.Count, "E").End(xlUp).Row)
    Set ws2 = Worksheets("Filtered Data")
    criteriaArray = Array("High", "Moderate-High")
  
    'Filter & copy
    If ws1.AutoFilterMode Then ws1.AutoFilter.ShowAllData
    With r
        .AutoFilter 1, 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

Thanks Kevin. The macro is working, however, there are a couple of issues that are occurring. The order of the columns has been shifted. Columns E and F were moved to A and B when pasted into the "FIltered Data" tab. It also appears that columns A,B,C, and D have been removed when the data is pasted into the "Filtered Data" tab. Are you noticing this when you run the macro?

I copied the headers and pasted them into the "Filtered Data" tab, but you can see the values do not align with the headers. The values for these headers cannot be found within the filtered data. Thanks.

 

[kevin9999]

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

3. What range (which columns) do you want copied into the newly created "Sheet1"

to which you replied:

3. Copy the newly filtered data and have it saved onto Sheet1.

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.