Simple question...

My idea is that when the user clicks a commandbutton, a userform is
displayed that is pulling dat from 2 workbooks. I wanted to use a
with.range function to populate some of the data from another workbook.
In workbook1, I do have a named range referencing data in workbook2,
however, when VBA hits that with range statement, it errors out since
the worksheet isnt an internal worksheet.

Is there a way with the "with range" statement to reference another
workbook? That workbook is open.

TIA

DS

Sub demo()
With Workbooks("Book1").Worksheets("Sheet1")
v = .Range("A1").Value
MsgBox (v)
End With
End Sub
--
Gary's Student


" wrote:

Simple question...

My idea is that when the user clicks a commandbutton, a userform is
displayed that is pulling dat from 2 workbooks. I wanted to use a
with.range function to populate some of the data from another workbook.
In workbook1, I do have a named range referencing data in workbook2,
however, when VBA hits that with range statement, it errors out since
the worksheet isnt an internal worksheet.

Is there a way with the "with range" statement to reference another
workbook? That workbook is open.

TIA

DS

It is nothing intrinsic about With that does this, it is just a matter of
fully qualifying the range with the book and sheet.

For example

MsgBox Workbooks("example.xls").Worksheets(1).Range("Bob" ).Rows.Count

With comes into its own when you want to repeatedly reference an object, so
by using with at the start you make the code more readable, and more
efficient, as the object model doesn't have to be fully resolved for each
command.

For example

Workbooks("example.xls").Worksheets(1).Range("A1") .Value = "Column 1"
Workbooks("example.xls").Worksheets(1).Range("B1") .Value = "Column 2"
Workbooks("example.xls").Worksheets(1).Range("C1") .Value = "Column 3"
Workbooks("example.xls").Worksheets(1).Range("D1") .Value = "Column 4"
Workbooks("example.xls").Worksheets(1).Range("E1") .Value = "Column 3"

becomes

With Workbooks("example.xls").Worksheets(1)
.Range("A1").Value = "Column 1"
.Range("B1").Value = "Column 2"
.Range("C1").Value = "Column 3"
.Range("D1").Value = "Column 4"
.Range("E1").Value = "Column 3"
End With

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

wrote in message
ups.com...
Simple question...

My idea is that when the user clicks a commandbutton, a userform is
displayed that is pulling dat from 2 workbooks. I wanted to use a
with.range function to populate some of the data from another workbook.
In workbook1, I do have a named range referencing data in workbook2,
however, when VBA hits that with range statement, it errors out since
the worksheet isnt an internal worksheet.

Is there a way with the "with range" statement to reference another
workbook? That workbook is open.

TIA

DS

Thanks a bunch!

I was drawing blank..