Hi,

I'm new to VBA programming and have been working on a generic input sheet
which will save details in different folders relevant to variables in a few
cells (questions via a userform completes these cells).

I've got most of the varibles to work when searching for the correct file
but I can't seem to get the below to work. Is this because it's a variale
itself and therefore not possible?


sectionfolder = Workbooks("KPI Advisor stat
sheet.xls").Worksheets("Stats").Range("C1") 'in this case Export
sectiontotalname = Workbooks("KPI Advisor stat
sheet.xls").Worksheets("Stats").Range("D1") ' IN this case Export Total
advisor = Workbooks("KPI Advisor stat
sheet.xls").Worksheets("Stats").Range("E1") ' In this case
Export 1
sectiontotalworksheet = Workbooks("KPI Advisor stat
sheet.xls").Worksheets("stats").Range("F1") ' In this case Total Export

Application.ScreenUpdating = False

' This one works fine.

Workbooks.Open Filename:="S:\Credit Management\Shared\KPI's\Section\" &
sectionfolder & "\Current\" & sectiontotalname & ".xls"

' but I can't get this variable to follow the correct path. I've tried with
and without the &'s and xls.

monthopen =
Workbooks(sectiontotalname).Worksheets(sectiontota lworksheet).Range("M1").Value


Any help would be much appricated.

Thanks
carl

On Feb 12, 10:13*am, Carlos wrote:
Hi,

I'm new to VBA programming and have been working on a generic input sheet
which will save details in different folders relevant to variables in a few
cells (questions via a userform completes these cells).

I've got most of the varibles to work when searching for the correct file
but I can't seem to get the below to work. Is this because it's a variale
itself and therefore not possible?

sectionfolder = Workbooks("KPI Advisor stat
sheet.xls").Worksheets("Stats").Range("C1") * * * * 'in this case Export
sectiontotalname = Workbooks("KPI Advisor stat
sheet.xls").Worksheets("Stats").Range("D1") * * * ' IN this case Export Total
advisor = Workbooks("KPI Advisor stat
sheet.xls").Worksheets("Stats").Range("E1") * * * * * * * ' In this case
Export 1
sectiontotalworksheet = Workbooks("KPI Advisor stat
sheet.xls").Worksheets("stats").Range("F1") ' In this case Total Export

Application.ScreenUpdating = False

' This one works fine.

Workbooks.Open Filename:="S:\Credit Management\Shared\KPI's\Section\" &
sectionfolder & "\Current\" & sectiontotalname & ".xls"

' but I can't get this variable to follow the correct path. I've tried with
and without the &'s and xls.

monthopen =
Workbooks(sectiontotalname).Worksheets(sectiontota lworksheet).Range("M1").V*alue

Any help would be much appricated.

Thanks
carl

Carl,

I am no expert. But my guess is that you have to check how you have
defined the variables. For intance are both "sectiontotalname" and
"sectiontotalworksheet" defined as strings? If they are it should
work.

If you want a workaround (probably get slaughtered by everyone in the
forum for this) I suggest you try this:

monthopen =
Workbooks("" & sectiontotalname).Worksheets("" &
sectiontotalworksheet).Range("M1").V*alue

Lastly are have you defined the monthopen in a proper way for the
value (I am guessing it is a number so integer, or long should work).

i.e "Dim monthopen as integer"

If you copy out the error message that appears maybe I could be more
helpful.

Best regards,

hi Both,

Thanks for the help,

I manged to get round it in the end by creating another variable which
referance the sheet i was looking for

monthtotal = "" & monthopen & ".xls"


Worksheets("Master").Select
Sheets("Master").Copy After:=Workbooks(monthtotal).Sheets(1)

I'm still learning, in fact this time last week I hadn't even looked a VBA
coding so it's be a fast learning curve. I've read a little about the "DIM"
function but don't really understand that side of it yet, so it's possibly
that this would have worked but I'm yet to cover that ground. can you
recomend any books or websites which help with explaining everything? I use
the F1 key for definitions but sometimes these are just as complicated to
understand.

Thanks for your help on this!!

Carl

Don't use this awkward construction:

"" &

Define your variables as strings or use CStr() so they don't need to be
implicitly coerced.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Carlos" wrote in message
...
hi Both,

Thanks for the help,

I manged to get round it in the end by creating another variable which
referance the sheet i was looking for

monthtotal = "" & monthopen & ".xls"


Worksheets("Master").Select
Sheets("Master").Copy After:=Workbooks(monthtotal).Sheets(1)

I'm still learning, in fact this time last week I hadn't even looked a VBA
coding so it's be a fast learning curve. I've read a little about the
"DIM"
function but don't really understand that side of it yet, so it's possibly
that this would have worked but I'm yet to cover that ground. can you
recomend any books or websites which help with explaining everything? I
use
the F1 key for definitions but sometimes these are just as complicated to
understand.

Thanks for your help on this!!

Carl

Does this provide a string that matches the path and filename of an existing
file:

"S:\Credit Management\Shared\KPI's\Section\" & sectionfolder & "\Current\" &
sectiontotalname & ".xls"

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Carlos" wrote in message
...
Hi,

I'm new to VBA programming and have been working on a generic input sheet
which will save details in different folders relevant to variables in a
few
cells (questions via a userform completes these cells).

I've got most of the varibles to work when searching for the correct file
but I can't seem to get the below to work. Is this because it's a variale
itself and therefore not possible?


sectionfolder = Workbooks("KPI Advisor stat
sheet.xls").Worksheets("Stats").Range("C1") 'in this case Export
sectiontotalname = Workbooks("KPI Advisor stat
sheet.xls").Worksheets("Stats").Range("D1") ' IN this case Export
Total
advisor = Workbooks("KPI Advisor stat
sheet.xls").Worksheets("Stats").Range("E1") ' In this case
Export 1
sectiontotalworksheet = Workbooks("KPI Advisor stat
sheet.xls").Worksheets("stats").Range("F1") ' In this case Total Export

Application.ScreenUpdating = False

' This one works fine.

Workbooks.Open Filename:="S:\Credit Management\Shared\KPI's\Section\" &
sectionfolder & "\Current\" & sectiontotalname & ".xls"

' but I can't get this variable to follow the correct path. I've tried
with
and without the &'s and xls.

monthopen =
Workbooks(sectiontotalname).Worksheets(sectiontota lworksheet).Range("M1").Value


Any help would be much appricated.

Thanks
carl