right now i have a few variables explicitly set in a summary workbook:

filename$(1) = "[workbook1.xls]"
.........
to.
........
filename$(20) = "[workbook20.xls]"


what i want to do is list the filenames on a separate sheet in the summary
workbook so i can mange them more easily

how would i set the above variables to reference a cell's value (b1:b20) on
that sheet?

thanks
--


Gary

Hi Gary,

Could you assign the listed names to an object variable and then operate on
the object variable?

Perhaps, something like:

Sub Tester01()
Dim WB As Workbook
Dim SH As Worksheet
Dim i As Long

Set SH = Sheets("Sheet1")
For i = 1 To 20
With SH
Set WB = Workbooks(.Cells(i, "B").Value)
' Process the workbook

'and move on to next workbook
End With
Next i
End Sub

---
Regards,
Norman



"Gary Keramidas" wrote in message
...
right now i have a few variables explicitly set in a summary workbook:

filename$(1) = "[workbook1.xls]"
........
to.
.......
filename$(20) = "[workbook20.xls]"


what i want to do is list the filenames on a separate sheet in the summary
workbook so i can mange them more easily

how would i set the above variables to reference a cell's value (b1:b20)
on that sheet?

thanks
--


Gary

Dim vformula
vformula = Range("B1:B20")

...

MsgBox vformula(3,1)


it loads the array dynamically, but is a 2D array.


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Gary Keramidas" wrote in message
...
right now i have a few variables explicitly set in a summary workbook:

filename$(1) = "[workbook1.xls]"
........
to.
.......
filename$(20) = "[workbook20.xls]"


what i want to do is list the filenames on a separate sheet in the summary
workbook so i can mange them more easily

how would i set the above variables to reference a cell's value (b1:b20)
on
that sheet?

thanks
--


Gary

bob:

that doesn't seem to work, get and error, expected array.

anyway, i think it's more complicated than that

i list each file name in the code:
FileName$(1) = "[Natalie.xls]"
thru filename 20

there is separate code for each month, so i have the 20 filenames listed 12
separate times in each months code.
then it loops through 20 times in each cell to create one formula and does
this 10 times for each month.
Range("B4").Formula = Range("B4").Formula & "+'" & fPath & FileName$(i) &
"Trans'!$M4"

i want to just list the file names once, and no matter which month it is,
it gets the file names form a separate sheet, names, in cells b1:b20

thanks

--


Gary


"Bob Phillips" wrote in message
...
Dim vformula
vformula = Range("B1:B20")

...

MsgBox vformula(3,1)


it loads the array dynamically, but is a 2D array.


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Gary Keramidas" wrote in message
...
right now i have a few variables explicitly set in a summary workbook:

filename$(1) = "[workbook1.xls]"
........
to.
.......
filename$(20) = "[workbook20.xls]"


what i want to do is list the filenames on a separate sheet in the
summary
workbook so i can mange them more easily

how would i set the above variables to reference a cell's value (b1:b20)
on
that sheet?

thanks
--


Gary

i just ended up using this

Fname = Worksheets("tellers").Cells(i, 2).Value

For i = 2 To 20
Range("B4").Formula = Range("B4").Formula & "+'" & fPath & Fname &
"Trans'!$F4"
more lines like above
next i

--


Gary


"Bob Phillips" wrote in message
...
Dim vformula
vformula = Range("B1:B20")

...

MsgBox vformula(3,1)


it loads the array dynamically, but is a 2D array.


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Gary Keramidas" wrote in message
...
right now i have a few variables explicitly set in a summary workbook:

filename$(1) = "[workbook1.xls]"
........
to.
.......
filename$(20) = "[workbook20.xls]"


what i want to do is list the filenames on a separate sheet in the
summary
workbook so i can mange them more easily

how would i set the above variables to reference a cell's value (b1:b20)
on
that sheet?

thanks
--


Gary

Should work fine. You wall get a type mismatch error if you try to use a
string variable, it needs to be variant,

Dim filename

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Gary Keramidas" wrote in message
...
i just ended up using this

Fname = Worksheets("tellers").Cells(i, 2).Value

For i = 2 To 20
Range("B4").Formula = Range("B4").Formula & "+'" & fPath & Fname &
"Trans'!$F4"
more lines like above
next i

--


Gary


"Bob Phillips" wrote in message
...
Dim vformula
vformula = Range("B1:B20")

...

MsgBox vformula(3,1)


it loads the array dynamically, but is a 2D array.


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Gary Keramidas" wrote in message
...
right now i have a few variables explicitly set in a summary workbook:

filename$(1) = "[workbook1.xls]"
........
to.
.......
filename$(20) = "[workbook20.xls]"


what i want to do is list the filenames on a separate sheet in the
summary
workbook so i can mange them more easily

how would i set the above variables to reference a cell's value
(b1:b20)
on
that sheet?

thanks
--


Gary