VBA - Filter by multiple values in one column in Sheet1. Values are a conditionally selected range in Sheet2 based on a date value in another column

[Ashkelon]

I was recently given the task of maintaining a rather complicated database with really confusing formatting. Once a week, i run a macro to transfer new tabular data that vary in the number of rows from another workbook to this database. The following is a sample part of the data that was copied over and the code used. The account numbers have no standard format.

Dim As Worksheet
    Dim Database As Worksheet
    Dim LastRow As Long
    Dim OtherLastRow As Long
            
    Set NewData = Workbooks("Workbook1").Worksheets("Sheet1")
    Set DataBase = Workbooks("Workbook2").Worksheets("Sheet2")
    
    'Finds the last used row in NewData
    LastRow = NewData.Cells(New_Deactivation.Rows.Count, "B").End(xlUp).Row
    
    'Finds the last used row in DataBase
    OtherLastRow = DataBase.Cells(DataBase.Rows.Count, "B").End(xlUp).Offset(1).Row
    
    'Copy & Paste new data to below the last row of database 
    NewData.Range("A2:Z" & LastRow).Copy _
        DataBase.Range("A" & OtherLastRow)

After the transfer is done, I'll need to go to another sheet of this database where another table is located. There is a Column C that also contains account numbers.

I want to filter this column with the account numbers of the newly pasted data. However, each cell is only chosen as the filter value if the date in Column H of the same row is outside the current financial year of 1st April 2020 to 31st March 2021. (e.g. rows 800 to 803 in the sample table is selected but not 804)

This is where i'm stuck after "selecting" the range. I thought of using an advanced filter with an array of conditionally defined entries but that is way out of my admittedly beginner level VBA skill set.

Set rCopy = NewData.Range("A2:V" & LastRow)
Set rDest = DataBase.Range("A" & OtherLastRow)
rCopy.Copy rDest
Set rDest = rDest.Resize(rCopy.Rows.Count, rCopy.Columns.Count)
rDest.Columns(7). ???

 

[ExcelGzh]

Ashkelon

Try some code that resembles this

'Set an autofilter in the row that contains the column headings on the first sheet (row 1 assumed here)
Rows("1:1").AutoFilter
'Set an autofilter criteria for the second column of the nominated range. [c] means the last column in your range, [r] means the last row in your range
ActiveSheet.Range("$A$1:$[c]$[r]").AutoFilter Field:=2, Criteria1:=">=1/04/2021", Operator:=xlOr, Criteria2:="<=31/03/2020"
'Copy range of cells
Range("A2:[c][n]").Copy
'Select the other sheet
Sheets("Sheet2").Select
'Paste the filtered data
ActiveSheet.Paste

 

[Ashkelon]

Ashkelon

Try some code that resembles this

'Set an autofilter in the row that contains the column headings on the first sheet (row 1 assumed here)
Rows("1:1").AutoFilter
'Set an autofilter criteria for the second column of the nominated range. [c] means the last column in your range, [r] means the last row in your range
ActiveSheet.Range("$A$1:$[c]$[r]").AutoFilter Field:=2, Criteria1:=">=1/04/2021", Operator:=xlOr, Criteria2:="<=31/03/2020"
'Copy range of cells
Range("A2:[c][n]").Copy
'Select the other sheet
Sheets("Sheet2").Select
'Paste the filtered data
ActiveSheet.Paste

Thanks for the help! This completely answered my problem

 

[ExcelGzh]

Glad I could help. Good luck with it.