"Using A Variable On Several Sheets"
 

Hi There,

Hope I can explain this correctly...I have a variable that was assigned a
numerical value using code identifying the last row used on "Sheet1". The
value of the variable changes as data changes on "Sheet1". I need to be able
to pick up this numerical value in code for several other sheets in the same
WB. Is there a method to store this variable so as to be able to use it from
all sheets in the WB?

Probably something very simple but I'm still learning....:)

TIA....Don

"Using A Variable On Several Sheets"
 

Declare the variable as Public at the top of a standard module instead of
inside a procedure. Then when your code modifies it, the other code can also
refer to its value. For example:

Public myRow As Long

Sub Procedure1()
myRow = 100
End Sub

Sub Procedure2()
MsgBox myRow
End Sub

The variable's value will persist as long as the workbook is open or until
an unhadled error occurs.

Greg


"Don" wrote:

Hi There,

Hope I can explain this correctly...I have a variable that was assigned a
numerical value using code identifying the last row used on "Sheet1". The
value of the variable changes as data changes on "Sheet1". I need to be able
to pick up this numerical value in code for several other sheets in the same
WB. Is there a method to store this variable so as to be able to use it from
all sheets in the WB?

Probably something very simple but I'm still learning....:)

TIA....Don

"Using A Variable On Several Sheets"
 

Thanks for the quick reply Greg....really appreciate your help.

Don

"Greg Wilson" wrote:

Declare the variable as Public a
t the top of a standard module instead of
inside a procedure. Then when your code modifies it, the other code can also
refer to its value. For example:

Public myRow As Long

Sub Procedure1()
myRow = 100
End Sub

Sub Procedure2()
MsgBox myRow
End Sub

The variable's value will persist as long as the workbook is open or until
an unhadled error occurs.

Greg


"Don" wrote:

Hi There,

Hope I can explain this correctly...I have a variable that was assigned a
numerical value using code identifying the last row used on "Sheet1". The
value of the variable changes as data changes on "Sheet1". I need to be able
to pick up this numerical value in code for several other sheets in the same
WB. Is there a method to store this variable so as to be able to use it from
all sheets in the WB?

Probably something very simple but I'm still learning....:)

TIA....Don

"Using A Variable On Several Sheets"
 

I should mention that it only needs to be Public if the other code is in
different modules (e.g. worksheet class modules, ThisWorkbook module,
userform modules). If the other code is all in the same standard module, you
can simply declare it at the top of the module thus:

Dim myRow as Long

Greg

"Don" wrote:

Thanks for the quick reply Greg....really appreciate your help.

Don

"Greg Wilson" wrote:

Declare the variable as Public a
t the top of a standard module instead of
inside a procedure. Then when your code modifies it, the other code can also
refer to its value. For example:

Public myRow As Long

Sub Procedure1()
myRow = 100
End Sub

Sub Procedure2()
MsgBox myRow
End Sub

The variable's value will persist as long as the workbook is open or until
an unhadled error occurs.

Greg


"Don" wrote:

Hi There,

Hope I can explain this correctly...I have a variable that was assigned a
numerical value using code identifying the last row used on "Sheet1". The
value of the variable changes as data changes on "Sheet1". I need to be able
to pick up this numerical value in code for several other sheets in the same
WB. Is there a method to store this variable so as to be able to use it from
all sheets in the WB?

Probably something very simple but I'm still learning....:)

TIA....Don