I've got an Excel workbook which I need to update frequently. In the workbook there are two sheets: Sheet 1) Employees who have not filled their monthly time sheets and Sheet 2) Employees who have.
Towards the end of the month, I have to track down the list of employees who have not filled in their time and remind them.
Say for example in Sheet #1) we have:
John Doe #1
John Doe #2
John Doe #3
John Doe #4
And in sheet Sheet #2) we have:
John Smith #5
John Smith #7
There are more than 150 employees to keep track of. So it's not just a matter of calling John Doe #1-4 - it's just my example. At the end of the month, I have to identify several dozen people who have not filled in their times and nag them until they do.
Here is what's happening now. About the 23rd of each month, I download an excel sheet from our network, and it spits out a complete list of all the people in our department. There are several columns besides their employee ID, Name, etc.
The very last col. I'll call it "X Hours" is the criteria column in question. This most important col shows the number of hours they have filled in for the current MONTH. If it shows 0 (zero) it means they have filled in all their hours for the month, a minus number (ex: -40) means they are missing one weeks worth of time; a plus number (20) means they have worked 20 hours overtime during the current month.
So here's what I need: On the last Friday of each month, I once again D/L the excel sheet from the network. I compare it to the first one I D/L on the 23rd, and see who has still NOT filled in their monthly time record. Then I manually update the original spreadsheet I retrieved on the 23rd.
How can you get VBA to compare 2 workbooks and move a row in another workbook based on a column criteria?
Compare April 23_Original.xls to: April 28_changes.xls
I need VBA to look at each employee listed in Apr 28 workbook, if last col. ("X Hours") shows a 0 or a positive number, get the employee ID shown on column 1, then go to April 23_Original.xls, search for his/her ID in Sheet #1, and move that employee (actually that entire row) to Sheet #2.
Both files will be open and I'm using Office 2000. Thanks and sorry for the long posting.
Reply With Quote
