Import data from closed workbooks

I have a workbook called "Adherence Marco" that has all my agents on it under worksheet("ART Yearly Numbers"). My agents save their weekly sheet as their name in a folder on the server. I would like a macro that will open each workbook which will be named for the agent (example: Eva Yates) & count the numbers from from Column F and put into workbook("Adherence Macro").Sheets("ART Yearly Numbers") after it looks up the name in column A and puts it in the corresponding row if they did the Kill captain (CC), AR (AR), Review (AR - Review), Reworked (Reworked Tickets), Container Moves (Container Moves), Triage Inbox (Triage Inbox). The weekly log sheet named as the agents name (Eva Yates) will change the date based off the week, so my macro would pull there data and enter it into the sheet "ART Yearly Numbers" as that is for the entire year and will not change. On Sunday, weather the agents workbook is open or closed (ex. Eva Yates), I would like for the sheet to be reset for the new week with all the data already pulled over the the adherence macro sheet!

Any help would be greatly appreciated & I have attached both same sheets for your reference - Thank you in advance for any help!

This assumes;
1) All workbooks including "Adherence Macro.xlsm" are in the same folder.
2) All "agents" workbooks have common worksheet name "Sheet1"
3) Retrieve data from F6,G6,H6,J6 & K6

Please Login or Register  to view this content.

Ok so a few issues I have found. The code you provided only puts zeros in the cells. The date at the top of the form puts the date for the Monday of the current week, its not the actual date, because we base it off the start of the week. I added a few lines so I have uploaded the current edited version and shouldn't it be retrieving data from E6:K6? Thank you for your help so much!

Ok, still some of the numbers are not matching up - open the sheets as I have saved some data and so you can see what it is doing. The Cell which is known as "myData" is the start of the week. Depending on the date the agent puts in the column A, it will count which day to show under. I have uploaded both sheets for your reference. Thank you again for all your help!

OOps, missed "c".

Please Login or Register  to view this content.

Just looked at your last workbooks.

How do you count row 10 of Sheet1?
Is it CC = 2 or CC = 1 and AR = 1 ?
What is "Primary Function"?

Ok, there are a few issues here! The date in D4 in the Workbook("Eva Yates") is the start date of the current week. This will change every week. When I run this macro it is putting the date from Workbook("Eva Yates").Range("D4"), I need the dates to be based off the column B.

It is also putting the counts incorrectly. So if you look at workbook("Adherence Macro").Range("DY94:DY99), this is for May 7th however today is 5/9/18 and it is putting the numbers in for May 7th (DY94:DY99) - they would need to be in Range("EA94:EA99").

What needs to happen is the following:
If CC (Column F) has a 1 in it in Workbooks("Eva Yates"), then in Workbook("Adherence Marco").Range("EA94")
If AR (Column G) has a 1 in it in Workbooks("Eva Yates"), then in Workbook("Adherence Marco").Range("EA95")
If Review (Column H) has a 1 in it in Workbooks("Eva Yates"), then in Workbook("Adherence Marco").Range("EA96")
If Review (Column I) has a 1 in it in Workbooks("Eva Yates"), then in Workbook("Adherence Marco").Range("EA97")
If Container Moves (Column J) has a 1 in it in Workbooks("Eva Yates"), then in Workbook("Adherence Marco").Range("EA98")
If Triage Inbox (Column K) has a 1 in it in Workbooks("Eva Yates"), then in Workbook("Adherence Marco").Range("EA99")

It would need to count the entire column from Row 9 down and put that count in for the current day (Today = 5/9/18), it would put it in Workbooks("Adherence Macro").Range("EA94") as the count.

Thank you so much for your help!

See my previous post.

And one more to add.

Once the code is run, the data in Sheet1 in each workbooks will be cleared?

Sorry about that - so the agent puts 1 in whatever they are doing. That was a mistake it was supposed to be CC (Range("F10") = 1 then it would be CC = 1 and AR = 1. Primary function reads from the top row (Row 8) and puts what the agent worked on so they don't have to keep scrolling back to the top!

Also it should be reading the date from column B from Workbook("Eva Yates") to place those numbers in the correct column in the workbook("Adherence Macro")

Now I think I understand what you are trying to do.

The most important thing is if the data in Sheet1 in each workbook will be cleared or not after the macro.
If not, the code will keep adding to the same cell(s) each time you run the code.
You know what I mean?
Or do you have any idea to stop this?

I rather have it run and overwrite any data in the cells that have already been written to workbook("Adherence Macro"), and on Monday when they open the book for it to clear all the cells from Row 9 down! So could we do in the worksheet on cell change of Range("D4"), because when the agent opens the workbook on Monday that cell will change to the current Monday's date.

OK, see if this works as you expected.

Please Login or Register  to view this content.

The only problem I see is that its not entering the data from the 10th so its not counting row 10 and then in row 16 I put 5/9/18 as the date and put a 1 in Range("J16") which it is not counting either! Any ideas?

If you have blank row(s) in col.B then

Please Login or Register  to view this content.

Please review the attached sheets! For some reason it is not ready the 10th of May correctly. I have added new data into the sheets for your reference - I appreciate all your help so much!

I am also getting an error at this line:

Please Login or Register  to view this content.

It suppose to be just "Review", not "AR Review" H8 in Sheet1.
Change to

Please Login or Register  to view this content.

Originally Posted by Jamesraywebber85

I am also getting an error at this line:

Please Login or Register  to view this content.

I don't get error.

H8 in sheet 1 is "Reworked Tickets"? I change the "AR Review" to "Review" & I am still getting the error! Any ideas?

Are we talking about the same file?

Your latest "Eva Yates.xlsm" Sheet!H8 is now "AR Review", so the changed code should work for this workbook.

I am so sorry, you are correct - I removed column A because I don't need to count them since we are using the formula for the counts - I have the new attached sheet for you!

No more change in "Eva Yates.xlsm"?

No more - that should be the last change - again I am sorry for the confusion.

This should do.

Please Login or Register  to view this content.

It is getting correctly here.

Attached is the result with your latest "Eva Yates.xlsm".

What about the "Reworked Tickets?" I do not see them counting now?

Then change to

Please Login or Register  to view this content.

Ok the dates are starting to line up but it looks like it is not counting all the correct numbers. I have included screen shots of what I am getting here so you can see.
Adherence Report.PNGEva Yates.PNG

Which "Eva Yates.xlsm" are you talking about?

Your latest one doesn't have "CC", "Kill Captain", on 5/10.
Upload "Eva Yates.xlsm" that you are working with and stick to it, otherwise, wasting of time.

This is the sheet I am using!

This is the result

This is my results! Any idea why we are both getting different results?
Capture.PNG

It looks like it is not counting the row for container moves.

Can you post the code that you are using?

This is the code you provided to me:

Please Login or Register  to view this content.

No, that's not the one I have last posted.

Use the one in post #30 in previous page.

Perfect! Thank you so much for your help - it works great! I'm sorry for the confusion! Thank you again so much!

OK, test the code hard and,

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

I tried moving this folder that has the Workbook("adhearence macro") with all the agents workbooks into another location and now it is throwing a error on this line:

Please Login or Register  to view this content.

The error states "Run-Tim error '13':
Type mismatch

Any ideas?

Not really sure.
Try change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

That did not work either - I changed LR as Long to LR as Variant and it passes the error down to the line

Please Login or Register  to view this content.

any ideas?

When you debug, what is the value of "myMax"? Is it Error?

If so, it is something to do with your path.

You seem to opened a new thread, so do it there.

myMax = 1 then it errors out at the next line

Please Login or Register  to view this content.

LR = 0

I greatly appreciate all your help however I ran into an issue that I need your assistance with. I made some changes to the workbook & it is not lining up correctly. The macro you assisted with is not counting the numbers correctly. Please see the attached workbook and see what you can help with. Based on workbook attached (Katarzyna Kobus) she has the following:

11/6/2018 - Reworked Tickets
11/6/2018 - CC (Kill Captain)
11/8/2018 - AR
11/9/2018 - Review
11/10/2018 - Triage Inbox

Which would relay to the "Captain" workbook in the following cells on the ART Yearly Numbers sheet:

11/6/2018 - Reworked Tickets = KZ54
11/6/2018 - CC (Kill Captain) = KZ50
11/8/2018 - AR = LB51
11/9/2018 - Review = LC52
11/10/2018 - Triage Inbox =LD53

Please Login or Register  to view this content.

This is the code that I am using which worked fine, however we got rid of the "Container Moves" & now everything is being thrown off. Can you please advise!?