swjtx99
asked on
Fill in missing rows using Date column
I am trying to fill in missing data based on a Date column from one sheet to another.
In Sourcebook1.xlsx, there are rows of data with a date in column E
In DestBook1.xlsx, there are rows of data with a date also in column E.
What I want is to copy the group of rows from sourcebook1 to Destbook1 for any dates that do not exist in Destbook1.
For example, in DestBook1, there are no rows with a date of 7/30/16. In Sourcebook1, there are 762 rows with a date of 7/30 so I want to copy those 762 rows to DestBook1 after the last current row, then resort the sheet so the data is sorted by date.
DestBook1 also has no rows with a date of 4/9/2016 so again, copy the rows from SourceBook1 to DestBook1.
Lastly I need a message box that says either:
"No rows were copied" (meaning there were no missing dates in DestBook1)
Or "Missing Dates 4/9/16, 7/30/16 were copied".
Hope I explained this clearly and thanks for your help in advance.
swjtx99
DestBook1.xlsx
SourceBook1.xlsx
In Sourcebook1.xlsx, there are rows of data with a date in column E
In DestBook1.xlsx, there are rows of data with a date also in column E.
What I want is to copy the group of rows from sourcebook1 to Destbook1 for any dates that do not exist in Destbook1.
For example, in DestBook1, there are no rows with a date of 7/30/16. In Sourcebook1, there are 762 rows with a date of 7/30 so I want to copy those 762 rows to DestBook1 after the last current row, then resort the sheet so the data is sorted by date.
DestBook1 also has no rows with a date of 4/9/2016 so again, copy the rows from SourceBook1 to DestBook1.
Lastly I need a message box that says either:
"No rows were copied" (meaning there were no missing dates in DestBook1)
Or "Missing Dates 4/9/16, 7/30/16 were copied".
Hope I explained this clearly and thanks for your help in advance.
swjtx99
DestBook1.xlsx
SourceBook1.xlsx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You're welcome.
In that case you only need to replace the following lines of code...
This Line
AND
This Line
In that case you only need to replace the following lines of code...
This Line
sws.Range("A2:E" & slr).SpecialCells(xlCellTypeVisible).Copy ws.Range("A" & Rows.Count).End(3)(2)sws.Range("A2:O" & slr).SpecialCells(xlCellTypeVisible).Copy ws.Range("A" & Rows.Count).End(3)(2)AND
This Line
ws.Range("A2:E" & lr).Sort key1:=ws.Range("E2"), order1:=xlAscending, Header:=xlGuessws.Range("A2:O" & lr).Sort key1:=ws.Range("E2"), order1:=xlAscending, Header:=xlGuessASKER
That worked. Thanks again,
You're welcome.
ASKER
This is great!
Can you advise how I can copy more columns? The example only had 5 columns but the production version has 15 (up to column O)
Thank you very much for your help!
swjtx99