VBA evaluate code error

I am offically going to go crazy! LOL. I have some VB code that is in a seperate workbook that I run via a button placed on the active worksheet. Here is the relevant section of code that "sometimes" causes issues.


The workbook is located on sharepoint but it would be open any time this macro is ran. I personally never have any issues at all with this macro, but others do. For some people it will work once but then it throws debug errors saying Object can not be found. The file open is named the exact same as indicated in the code. The rest of the code finds the workbook, so any idea why maybe the problem section "sometimes" throws a unable to locate object error?


I probably have some duplicate code that isn't nessecary as I tend to go in and add little bits here and there. Portions of the code were helped to be put together with some forum members and I appredicate that. I probably have a few extra activate lines but for some reason even tho the With's would refer to a certain sheet it wouldnt actually do the tasks on that sheet unless I activated it.

Please Login or Register  to view this content.

This is the complete code just in case there is something else causing the problem.

Please Login or Register  to view this content.

First up - dead honest I've not looked at this in depth so apologies in advance if the below is of no consequence...

Evaluate will process ranges against the active sheet unless otherwise specified.
Assume range specified of Sheet2!A1:A10 ... if I use this range within Evaluate whilst Sheet1 is active, eg:

Please Login or Register  to view this content.

Sheet2!A1:A10 will actually mirror contents of Sheet1!A1:A10 ... to use Sheet2 contents you must reference the appropriate parent object eg:

Please Login or Register  to view this content.

the above would thus use Sheet2 when processed

it's not immediately obvious given sheet names whether or not the above is the case here or not (ie active sheet and r are not the same)