This should be an easy question

If I have multiple workbooks open.
Each workbook is assigned to a var. such as
WB1, WB2, etc...

If I have a name range. Such as "NameRange1"
How do I get the values from each workbook. Without knowing the worksheet.
Range("NameRange1") seems to get only the active workbook.
There does not seem to be a WB1.Range method. On the workbook.
Can you do this without Activate on each workbook?

thanks

dim wb as workbook
dim ws as worksheet

for each wb in workbooks
for each ws in wb.worksheets
ws.range("NameRange1")...
next ws
next wb

"greg" wrote:

This should be an easy question

If I have multiple workbooks open.
Each workbook is assigned to a var. such as
WB1, WB2, etc...

If I have a name range. Such as "NameRange1"
How do I get the values from each workbook. Without knowing the worksheet.
Range("NameRange1") seems to get only the active workbook.
There does not seem to be a WB1.Range method. On the workbook.
Can you do this without Activate on each workbook?

thanks

Ah,
You have to go through the worksheet.
Even though a named range is unique to the workbook.


"Sam Wilson" wrote in message
...
dim wb as workbook
dim ws as worksheet

for each wb in workbooks
for each ws in wb.worksheets
ws.range("NameRange1")...
next ws
next wb

"greg" wrote:

This should be an easy question

If I have multiple workbooks open.
Each workbook is assigned to a var. such as
WB1, WB2, etc...

If I have a name range. Such as "NameRange1"
How do I get the values from each workbook. Without knowing the
worksheet.
Range("NameRange1") seems to get only the active workbook.
There does not seem to be a WB1.Range method. On the workbook.
Can you do this without Activate on each workbook?

thanks

Not sure what you are after, but this will return the sheet and cell
reference for a range named "test"


Sub dk()
MsgBox ThisWorkbook.Names("test").RefersTo
End Sub


"greg" wrote in message
...
Ah,
You have to go through the worksheet.
Even though a named range is unique to the workbook.


"Sam Wilson" wrote in message
...
dim wb as workbook
dim ws as worksheet

for each wb in workbooks
for each ws in wb.worksheets
ws.range("NameRange1")...
next ws
next wb

"greg" wrote:

This should be an easy question

If I have multiple workbooks open.
Each workbook is assigned to a var. such as
WB1, WB2, etc...

If I have a name range. Such as "NameRange1"
How do I get the values from each workbook. Without knowing the
worksheet.
Range("NameRange1") seems to get only the active workbook.
There does not seem to be a WB1.Range method. On the workbook.
Can you do this without Activate on each workbook?

thanks

code sample
Option Explicit
Sub check_range()
Dim nm As Name
Dim cell As Range
Dim wb As Workbook
For Each wb In Workbooks
Set cell = wb.Names.Item("range1").RefersToRange

'do something
cell.value = format$(now,"HH:MM:SS")

'don't need these three - these are just for demo
wb.Activate
cell.Parent.Activate
cell.Select
Next
End Sub


once your range object 'cell' is set to the range, you can utilize it
without it, or even the workbook being active



"greg" wrote in message
...
This should be an easy question

If I have multiple workbooks open.
Each workbook is assigned to a var. such as
WB1, WB2, etc...

If I have a name range. Such as "NameRange1"
How do I get the values from each workbook. Without knowing the
worksheet.
Range("NameRange1") seems to get only the active workbook.
There does not seem to be a WB1.Range method. On the workbook.
Can you do this without Activate on each workbook?

thanks