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:

Sub Reporting()
Dim wbname As String
Dim SName As String
    wbname = Range("D5").Value
    SName = Range("D7").Value
    
Workbooks.Open Filename:=wbname

With Sheets(SName)
    .Activate
End With

Call IMPORT
Call PILOT_STATS_UPDATE
Call BANDING_UPDATE
Call VP_UPDATE
Call TIDY_UP
'Call CLOSE_THIS_WB

End Sub

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:

With sheets(SName) <=== this is highlighted yello
.activate
End With

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.