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

New Member
Joined
Dec 19, 2020
Messages
18
Office Version
  1. 365
Platform
  1. Windows
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.

1608417461926.png


VBA Code:
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.

VBA Code:
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). ???
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

ExcelGzh

Board Regular
Joined
Mar 29, 2020
Messages
130
Office Version
  1. 365
Platform
  1. Windows
Ashkelon

Try some code that resembles this
VBA Code:
'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
 
Solution

Ashkelon

New Member
Joined
Dec 19, 2020
Messages
18
Office Version
  1. 365
Platform
  1. Windows
Ashkelon

Try some code that resembles this
VBA Code:
'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

Board Regular
Joined
Mar 29, 2020
Messages
130
Office Version
  1. 365
Platform
  1. Windows
Glad I could help. Good luck with it.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,755
Messages
5,597,929
Members
414,193
Latest member
bb60

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top