Hey guys -
I'm creating some user activity tracking, and one of my cells in a report grabs info from another workbook. I'm using the following code:
It's essentially saying search for the name "Roger" in cell "F9" of the "April 2011" worksheet in a workbook called "BDActivityCalendar_2011_Midwest.xls".
This code, when entered into a cell, prompts me to find the file manually, even though I'm stating the name of the file in the code. Not a problem if you're doing it once...but there are a hundred of these cells (F9-F109), in 5 columns (F, E, H, J, L). That's 500 cells per user. We'll have 25 users.
I thought I could set this up for 1 user, copy that worksheet, and use search and replace to streamline this. The problem is, if I change "Roger" to "Dave" in a global replace, I'm prompted 500 times to find the file. The filename is correct and it lies in the same directory. And when next month comes around, I'll need to replace "April 2011" with "May 2011", which will Excel will prompt me to find the file 500 times for 25 users. No good.
Is there any way around this? Is my syntax wrong in me code that Excel needs to go find the file, even though I'm supplying the location?
Bookmarks