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