Macro To Import Specific Data By Matching Data Between 2 Workbooks After User Picks File

Good afternoon,

I hope everyone in the community is doing well and enjoying 2016 so far. I'm in need of 2 macros to perform specific functions and was hoping someone could help. I will be as descriptive as possible in order to avoid confusion and make the task simpler to understand. They are as follows:

1. The first macro runs in workbook "OT TRACK" and prompts the user to pick a file. Once the file is picked, the macro displays the pay date located in cell "C3" of the picked work book (e.g. OT HOURS) in cell "B5" of "OT TRACK" and matches the locations in cells A7, A28, A44, A60, A76, A92, A108 and A124 with the sheets named the same in workbook "OT HOURS". When each sheet is found, it imports the employee name, current payroll OT and YTD OT for those employees into the first 3 columns of the respective tables (by location) in "OT TRACK". I'd also be obliged if the values displayed in column B for current OT are added to the existing value in the monthly actual for that row (JAN '16 ACT, FEB '16 ACT, etc.) based on the pay date. For example, if the pay date is 29th JANUARY 2016, then the value in column B against a name is incremented to the value in the JAN '16 ACT for that row. If the pay date is 9th FEBRUARY 2016, then it's incremented to FEB '16 ACT. I'm not sure if this is possible, but any help you can provide is greatly appreciated.

2. The second macro (also inside "OT TRACK") uses the names in the tables (that have already been imported by the previous macro) and compares the names with a file that the user picks. If a match is found in the sheets of this workbook (e.g. PAYROLL BUDGET 1), then the macro imports the budgeted hours for each month for that name (beginning from column G), subtracts 160 from it and displays it (only if positive) in JAN '16 BUD, FEB '16 BUD, etc. In the event that the name is found twice in the work book on different sheets, the budgeted hours should be added (column G of both sheets), 160 subtracted from it and displayed in JAN '16 BUD, etc. (Reference PAYROLL BUDGET 2 for this case)

I hope the explanation provided is clear and I sincerely appreciate and value all the help offered. I have attached the excel files in question for reference and aid.

Best wishes,
Hackboss

Hi Reuben,

Try the attached file, which should be pretty close to what you want. Please let me know what I did wrong, and what changes you need.

Rules for this file:
a. Pay Date MUST be in cell 'B5'.
b. Location Text can be on any row. Text MUST be in Column 'A' and the Cell Interior Color must be ORANGE.
c. Any ORANGE Location Cell that is BLANK, is ignored.
d. 'EMPLOYEE NAME' must be in Column 'A' exactly 3 rows below 'LOCATION'.
e. There MUST be enough room for New Names at each LOCATION. NO Error checking is done for this.
f. Employee Names can be in ANY ORDER.
g.The same 'Employee Name' in different locations is ALLOWED.
h. DUPLICATE 'Employee Name' in the same Location is NOT ALLOWED.

NOTE: Yellow Highlight on 'Status' Sheet indicates the most recent UPDATE.



Rules for 'OT HOURS' files:
a. Pay Date MUST be in cell 'C3' in each Sheet.
b. The 'Name' Header cell MUST be in Column 'D' on any row.
c. Names MUST be in Column 'D.
d. 'Total' MUST be the last item in Column 'D'
e. Current OT MUST be in Column 'E'.
f. YTD OT MUST be in Column 'G'.
g. Blank lines are allowed.
h.The same 'Employee Name' in different locations is ALLOWED.
i. DUPLICATE 'Employee Name' in the same Location is NOT ALLOWED.
j. Employee Names can be in any order.



Rules for 'Budget files:
a. Location MUST be in cell 'A3' on each Sheet.
b. There is a Limit of one Location per Sheet.
c. There can by many Sheets. Each Sheet is NOT REQUIRED to have the same Location.
d. Each Location MUST match a Location in the 'OT TRACK' file.
e. The following CASE INSENSITIVE header values must exist 'Name' (Cell 'A4') and 'Position' (Cell 'B4').
f. If used on an existing (pre Macro_ file, Budget values less than ZERO (when the number is actually negative) will result in faulty results.
g.The same 'Employee Name' in different locations is ALLOWED.
h. DUPLICATE 'Employee Name' in the same Location is NOT ALLOWED.
i. Employee Names can be in any order.



How the Software works for Actual Hours:
a. A simple check is done to verify that the files contain the proper format.
b. Normal Processing is done if the 'OT HOURS' file has NOT been previously processed.
c. The User is asked if UNDO is to be performed, if the 'OT HOURS' file HAS been previously processed.
d. All NON-BLANK 'ORANGE' Cells are identified on the 'OT TRACK' Sheet to obtain Locations.
e. All 'OT TRACK' Data is removed from Column 'B' (Current OT).
f. The 'Pay Date' from the 'OT HOURS' file, is put in cell 'B5' on the 'OT TRACK' Sheet.
g. Each Location is processed in the 'OT' HOURS file.
(1) Extra Locations in the 'OT HOURS' file are IGNORED.
(2) Missing Locations in the 'OT' HOURS file are NOTED in the processing Summary message.
(3) Columns A thru C (Employee Name, Current OT, YTD OT) are imported from the 'OT HOURS' file (one Employee at a time).
(4) If UNDO, was selected the Current OT numbers are NEGATIVE.
(5) The 'Current OT' value for each Employee is added to the ACTUAL OT value in the appropirate month.
h. The Pay Date and File Name are put in the Status Sheet sorted by Pay Date (Removed from the Summary Sheet if UNDO).



How the Software works for Budget Hours:
a. A simple check is done to verify that the files contain the proper format.
b. Normal Processing is done if the 'Budget' file has NOT been previously processed.
c. The User is asked if UNDO is to be performed, if the 'Budget' file HAS been previously processed.
d. All NON-BLANK 'ORANGE' Cells are identified on the 'OT TRACK' Sheet to obtain Locations.
e. Each Location (Sheet) in the 'Budget' File is processed.
(1) A list of 'Employee Names' and associated 'Row Numbers' in the 'OT TRACK' Sheet is created for this Location.
(2) Each row (Employee Name) for this Location (Sheet) in the 'Budget' File is processed (OT Hours are placed in the proper place in the 'OT TRACK' Sheet).
OT Hours are added (subtracted for UNDO) to the existing OT Hours for each Employee.
f. The File Name and Location Counts are put in the Status Sheet sorted by Location (Removed from the Summary Sheet if UNDO).

NOTE: Small fractional values are encoded/decoded in the 'OT TRACK' Sheet Budget Columns to look like zero but contain values that indicate the number of negative hours.



Simplified OT Hours Examples (January - EMP 1):
a. Total Hours of 200. OT Hours of 40 [= 200 - 160]. OT Hours displayed is 40.
b. Total Hours of 100. OT Hours of MINUS 60 [= 100 - 160]. OT Hours displayed is 0.00
There is a very small fractional value stored to indicate MINUS 60 = 0.00006



Negative Number Calculation Algorithm:
a. If the OT Hours is Positive, Use the OT Hours.
b. If the OT Hours is Negative, the value is ENCODED as follows:
(1) Make the value Positive (multiply by MINUS 1)
(2) Divide the value by 1,000,000 (One Million)
c. To decode a fractional value to actual OT Hours:
(1) If the value is greater than 0.002, use the value.
(2) Otherwise, multiply the value by 1,000,000 and change the sign.

Lewis

Hi Lewis,

Thank you for the detailed response and solution. I receive an error message "Terminating. There are NO Orange 'Location' cells in this file. Sheet Name:'Sheet1'" when I click the import "Actual OT Hours" button. I have verified that the colour of the location cells are actually orange but I haven't been able to get it to import the data. Any assistance you can provide is greatly appreciated. Thanks once again.

Best wishes,
Reuben

Hi Reuben,

I feel bad that you are having problems.

The colors are probably the wrong orange. All 8 Locations Cells in Sheet1 (TRACK OT) must be RGB (255,204, 0) which was the color in your original file.

In Excel 2010 (probably the same for Excel 2007):
a. Select one or more 'Location Cells':
b. Right Click the Cell
c. Format Cells > More Colors > Custom > RGB (255, 204, 0) > OK > OK


To copy colors from one Location Cell to another:
a. Left Click a Location Cell > Ctrl C
b. Select one or more destination Location Cells > Right Click a destination cell > Paste Special > Formats > OK

Please let me know if you have any more problems.

Lewis

Hi Lewis,

Thanks for your help. In fact, I'm sorry to keep bothering you. I checked the global orange value that you've set in the code and ensured that the colour is the same RGB value. However, initially it still presented me with the same error. I waited for a couple of hours and tried it again and this time it presented me with a different error message "Data Integrity Error: The "OT HOURS" sheet has a duplicate name in cell A47." I have checked all tabs on the sheet and cell A47 is blank, so I'm unable to decipher the cause of the issue. Thanks for all your help once again.

Best wishes,
Reuben

Hi Lewis,

Thanks for your help. In fact, I'm sorry to keep bothering you. I checked the global orange value that you've set in the code and ensured that the colour is the same RGB value. However, initially it still presented me with the same error. I waited for a couple of hours and tried it again and this time it presented me with a different error message "Data Integrity Error: The "OT HOURS" sheet has a duplicate name in cell A47." I have checked all tabs on the sheet and cell A47 is blank, so I'm unable to decipher the cause of the issue. Thanks for all your help once again.

Best wishes,
Reuben

I have seen the error previously when I was writing the software, but I did not write down why it happened, because I thought it would not occur with real data.

I can not reproduce the error at this point, but I know you are telling the truth, because I saw the exact same error.

I believe the error was caused by one or more of the 'Orange Cells' being blank. If one or more of the 'Orange Cells' are blank and you get the error, put anything in the 'Orange Cells' such as 'Not Applicable, 'This Location Not in Use', etc.

If the error persists, please upload a sample file that demonstrates the problem (the file with the macros), and the sample 'Hours OT' file that causes the problem. In addition ,please describe exactly which keysrokes (and/or mouseclicks) you used to make the problem appear.

I apologize again for the problems.

Lewis

Originally Posted by LJMetzger

I have seen the error previously when I was writing the software, but I did not write down why it happened, because I thought it would not occur with real data.

I can not reproduce the error at this point, but I know you are telling the truth, because I saw the exact same error.

I believe the error was caused by one or more of the 'Orange Cells' being blank. If one or more of the 'Orange Cells' are blank and you get the error, put anything in the 'Orange Cells' such as 'Not Applicable, 'This Location Not in Use', etc.

If the error persists, please upload a sample file that demonstrates the problem (the file with the macros), and the sample 'Hours OT' file that causes the problem. In addition ,please describe exactly which keysrokes (and/or mouseclicks) you used to make the problem appear.

I apologize again for the problems.

Lewis

Hi Lewis,

I apologize for the slight delay in getting back to you as I could not access the forum for a brief time due to internet issues. I have not used the code with the files I was intending as yet since I wastesting it out with the example files to check the operation and understand the logic that you have provided. I enjoy learning and understanding new material. I tried the suggestion you've provided above today and I was presented with the initial error once again which states "Terminating. There are NO Orange 'Location' cells in this file. Sheet Name:'Sheet1'." As mentioned previously, I was able to get past this error after waiting for a few hours and leaving the file open. I have verified that the location cell colour is RGB (255, 204, 0). On the initial run after waiting for some time and getting past the orange location error,I was presented with the "Data Integrity Error: There is a duplicate name in cell A47" when running the macro. I could not duplicate the data integrity error on today's run as I was stuck at the orange location cell error. The steps I took to arrive at the error is listed as below:

1. Open ExcelForumOTTrack file with the software code.
2. Then I clicked "Clear All OT Track Data In This File" button to clear Sheet 1 of any pre-populated data.
3. Click "Select New "OT Hours" Folder & File Name"
4. Select "OT Hours" file(same as previously provided)
5. Click "Import 'Actual OT' Hours into this file" button
6. Error Message: "Terminating. There are NO Orange 'Location' cells in this file. Sheet Name:'Sheet1'." or "Data Integrity Error: There is a duplicate name in cell A47"

Thanks once again for all your support and help. I am very grateful and sincerely appreciate the interest you've taken to help me out. Let me know if there is something I am doing incorrectly procedure wise and if there is a specific pattern I should follow. I have uploaded the files causing the issues with this post

Best wishes,
Reuben

Hi Reuben,

I downloaded your files, followed your instructions, and everything worked fine on your files.

Uncanny situation. It must be the difference between my Excel 2003 and my Excel 2010 and your Excel 2007 somewhere. We will figure it out. It may take a while.\

I am very busy tomorrow, but I'll try to have something on Friday that we can use to see where I went wrong, went wrong, went wrong.

Please be patient.

Lewis

Hi Lewis,

Thank you very much for your continued support to help me with my issue. I'm very grateful for everything that you have done so far. I'm sure that this takes a chunk of time and it can't be easy with your busy schedule and other obligations. Don't be hard on yourself. It's probably something simple and could also be something that I'm not following on my end. Thanks once again. Let me know what you find. Have a great day!

Best wishes,
Reuben

Hi Reuben,

I reworked the software to eliminate the need to search for 'ORANGE" which caused you a great deal of problems. Since the Orange cells were the only 'Merged Cells' on the Sheet, I now search for the 'Merged Cells'.

I removed all the 'Duplicate' checks which also caused you problems. I hope the 'Duplicate' check removal solves your other problems. I'm not sure, because I could not duplicate the problem. Hopefully, the problem you had is not a symptom of some other anomaly. If it is still a problem, we will work together to solve it.

Try the attached file.

Lewis

Hi Lewis,

Thanks a ton for the reworked sheet! It works as promised. I tested it with the test data and will now implement it with the actual data that I have. Thanks for being prompt and delivering as promised. Your support to help me out is always greatly appreciated and valued. I will let you know if I face any issues along the way or require some further guidance and assistance. Have a great day!

Best wishes,
Reuben