Defining Workbooks / Worksheets

[Ark68]

I go back and forth between several different workbooks and worksheets in the course of my VB project execution.

Is their a simple way of defining the different destinations rather than always having to type Workbooks("Workbook1").Worksheets("Sheet") ?

Jenn

 

[Colin Legg]

Hi Jenn,

One option is to use Worksheet Object variables:

Dim wstA as Worksheet
Dim wstB as Worksheet
 
Set wstA = Workbooks("Workbook1").Worksheets("Sheet1")
Set wstB = Workbooks("Workbook2").Worksheets("Sheet3")

From there on, you can refer to ranges (etc...) on those worksheets by using:

wstA.Range("A1").Value = 1
wstB.Range("E2").Value = "Hello"

etc...

Hope that helps...

 

[Ark68]

Thanks Colin!

 

[Ark68]

Will I have to define these in all my individual subs in which they are accessed, or is there a place to apply them globally?

 

[Colin Legg]

Do these subs call each other (ie. They all run together in a sequence), or do they run at separate times? Are they all contained in the same code module, or separate code modules?

 

[Ark68]

Some subs are called from each other, others are called when needed. Subs are scattered amongst a few modules.