Oops!

Set wkb As workbooks("MyWorkbook.xls")

should be:
Set wkb = workbooks("MyWorkbook.xls")

Best Regards,
Walt Weber

"Walt" wrote:

Hi Ed,

Not quite. In a code module (Not a sheet or thisworkbook module) try this:

Public wkb As Workbook, rng As Range

Sub SetRanges()
Set wkb As workbooks("MyWorkbook.xls")
Set rng = wkb .ActiveSheet.Range("A1:Z100")
End Sub

After running SetRanges you can then use either wbk or rng in any other sub
in the project to refer to MyWorkbook or that range.

If you only need to use those variables in code in the same code module, you
can use Dim vs. Public when declaring them above.

Best Regards,
Walt Weber


"Ed" wrote:

Bob: So if I have an assortment of subs in ThisWorkbook and Forms modules,
can I:
Sub SetRanges
Dim wkb As Workbook
Set wkb As MyWorkbook
Dim rng As Range
Set rng = MyWorkbook.ActiveSheet.Range("A1:Z100")
End Sub
and then use:
wkb.rng
in any other sub in the project to refer to that range?
Ed

"Bob Phillips" wrote in message
...

"Ed" wrote in message
...
David:

I have had only limited success trying to get Workbook,
Worksheet, and Range variables to Set in one Sub and carry over into
another. Referring to my first statement, I'm probably doing something
amiss; if so, I'm sure someone else here can give the best way.

Ed, this may be caused by decalring public variables in the workbook or
worksheet code modules, as they are not global to the project, they need
the
calss module identifier when referring to them, such as
Thisworkbook.myVar,
as they are essentialy class properties.