Range question

G

greg

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
 
S

Sam Wilson

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
 
G

greg

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

JLGWhiz

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
 
P

Patrick Molloy

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top