script to selecting correct sheet calculated via cell formula

**Kramxel** · 07-17-2013, 04:08 AM

I am having a few problems with this script:

Please Login or Register  to view this content.

cell D5 contains the formula: ="S:\Contact Centre Analyst\Adams\Tiger Data\Tiger Data - "&TEXT(MONTH(WORKDAY(TODAY(),-1))*29,"MMM")&" "&YEAR(WORKDAY(TODAY(),-1))&".xls"

D7 - =DAY(WORKDAY(TODAY(),-1))

When I step through the code step by step it works perfectly, but unfortunately when I run it normally, it doesn't appear to select the correct sheet. I'm thinking its because the references are calculated within the cell rather than the code itself (not entirely sure if this actually makes a difference or not).

Does anyone have a suggestion or two to resolve this?

**Kramxel** · 07-17-2013, 06:14 AM

I'm now getting an error of "Run Time Error 9 - subscript out of range at the step:

Please Login or Register  to view this content.

SName shout resolve to 16 in this particular instance which is both the NAME of the sheet as well as its CODENAME, I want to be sure to be referencing its name however, but have stared so long at this i'm tying myself in knots and getting rather confused.