vba macro dynamically use multiple criteria for filters across multiple columns to then copy and paste to other sheet

[Lisa Harris]

Hi all, really hoping someone can help. Spent a few days now and getting nowhere it feels, and it may just not be possible.

I have a workbook with the following:

- sheet 1: a master list - columns B4:J containing categories that we need to filter by and then columns K4:IY with the unique name, number and info we need to copy and paste into sheet3. ROW 4 are the column headers

- sheet 2: a criteria setting sheet. The users can select from drop down data validation lists as many criteria needed to filter each column. Range B9:J14 - row 9 being column headers). so the criteria to filter column B on sheet 1 will be listed in range B10:B14 on sheet 2. Not all criteria will be selected each time, so these dynamic cells will be blank at times. i.e. may only want to filter column B by 1 criteria, which we would put in B10. therefore B11:B14 and C10:J14 will be blank as no criteria needed to be selected

-sheet 3: the output - a filtered view of the master list. this sheet (Range J5:IX) needs to be cleared before the macro begins the filtering and pasting. The cells copied from sheet 1 need to be pasted in J5 onwards (formatting as well as values)


Have tried looping codes which work across columns but only for one criteria and not dynamic cell ranges. I have also tried many autofilter codes, but cannot seem to get these to work past 2 columns or get correct result at times. So I went back to finding a looping code. The following code works nicely for one column with the dynamic cells, and to overcome the blanks cells, I have added a default setting of 'please select' when the criteria cells aren't needed. However, cannot add in any more columns - all attempts I have made error or crash.

Sub Copy_Cells_Loop()

    
    Dim c As Range, lastrow As Long
 
    
    lastrow = Sheets("sheet1").Cells(Rows.Count, "K").End(xlUp).Row
    
    Application.ScreenUpdating = False
    


  Sheets("sheet3").Range("J5:IX10000").ClearContents
    


  For Each c In Sheets("sheet1").Range("A4:IY" & lastrow)
    
        If c.Value = Sheets("sheet2").Range("B10") Or c.Value = Sheets("sheet2").Range("B11") Or c.Value = Sheets("sheet2").Range("B12") Or c.Value = Sheets("sheet2").Range("B13") Then


        Sheets("sheet1").Range("K" & c.Row & ":IY" & c.Row).Copy Sheets("sheet3").Range("J" & c.Row).End(xlUp).Offset(1, 0)
       
       End If




 Next c


    
    Application.ScreenUpdating = True




End Sub

The most recent autofilter I tried by adapting some code from a recorded macro is as follows, but this empties all filters and therefore lists no data to copy:

Sub Copy_Cells_Loop()

    
    Dim c As Range, lastrow As Long
 
   Set OverallSheet = Sheets("sheet1") '
   Set StandardSheet = Sheets("sheet2") '
   Set FilteredSheet = Sheets("sheet3") '
 
 




    
    lastrow = OverallSheet.Cells(Rows.Count, "K").End(xlUp).Row
    
    Application.ScreenUpdating = False
    


  FilteredSheet.Range("J5:IX10000").ClearContents
    




        OverallSheet.Range("A4:IY" & lastrow).AutoFilter Field:=2, Criteria1:=Array( _
        StandardSheet.Range("B10"), StandardSheet.Range("B11")), Operator:=xlFilterValues
    
       OverallSheet.Range("A4:IY" & lastrow).AutoFilter Field:=3, Criteria1:=Array( _
        StandardSheet.Range("C10"), StandardSheet.Range("C11")), Operator:=xlFilterValues
        
        OverallSheet.Range("A4:IY" & lastrow).AutoFilter Field:=4, Criteria1:=Array( _
        StandardSheet.Range("D10"), StandardSheet.Range("D11")), Operator:=xlFilterValues
        
        OverallSheet.Range("K" & c.Row & ":IY" & c.Row).Copy FilteredSheet.Range("J" & c.Row).End(xlUp).Offset(1, 0)
        
    Application.ScreenUpdating = True






End Sub

Any help will be greatly appreciated! Many thanks in advance

 

[=ODIN=]

I think I understand what you're trying to do. I'd recommend researching advanced filters for ranges -- they allow you to filter multiple columns in a range with multiple criteria. Also, they allow you to specify where the output should be copied.

https://docs.microsoft.com/en-us/office/vba/api/excel.range.advancedfilter = Microsoft page
https://www.thespreadsheetguru.com/...automate-filtering-on-and-out-specific-values = Good description, just need to read down to the advanced filter section.

 

[Lisa Harris]

This is fantastic! a very useful website for the future too - thank you. Will give it a go.
Many thanks for this - much appreciated