Automatically Run a macro when a worksheet is selected
 

"Stephen Hartman" wrote in message ...
Is there a bit of code that will automatically run a macro when I select the worksheet that contains the macro?

That work's great thanks! One other question....

If I have 2 sheets, I need code in sheet 2 that will check the last row used in sheet 1 and then return that value as a variable so it can be used throughout the code in sheet 2. Any help would be greatly appreciated!

Automatically Run a macro when a worksheet is selected
 

"Stephen Hartman" wrote in message
...
If I have 2 sheets, I need code in sheet 2 that will check the last row
used in sheet 1 and then return that value as a variable so it can be used
throughout the code in sheet 2. Any help would be greatly appreciated!

Hi Stephen,

If you mean you want the last row number, here's one way to do it. Note
that in some cases the UsedRange method will return an area greater than the
actual area in use, in which case a more complex method of determining the
last row is required.

-------------------------
In Sheet2 Code Module
-------------------------
Private mlLastRow As Long

Private Sub Worksheet_Activate()
mlLastRow = Sheet1.UsedRange.Rows.Count
End Sub

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *

Automatically Run a macro when a worksheet is selected
 

Hi Stephen,

Put the following in a standard module in your project (i.e. not in one of
the worksheet code modules):

Function LastRowSheet1() As Long
LastRowSheet1 = Sheet1.UsedRange.Rows.Count
End Function

Now you can enter the following in any cell on Sheet2 and you will get the
last row number on Sheet1 (subject to the previously mentioned limitations
in the UsedRange method):

=LastRowSheet1()

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *


"Stephen Hartman" wrote in message
...
Let me try again, Rob as I am still struggling with getting that formula
to
work.

Sheet 1 "Wine Inventory" is varying in length and will change monthly.

Sheet 2 "Wine Location" is linked to sheet 1. I would like a function
that
checks the last row used in sheet 1 and returns value of say "last row
used
= Rownum" or "Rownum = 400".

At this point I can plug in the code of sheet 2 the variable of "rownum"
and
the function above will have actually figured out what the value of
"romnum"
is.

Currently I am manually checking the last row used and insertingt it into
the macro in sheet 2 (silly huh?)

Automatically Run a macro when a worksheet is selected
 

Rob, the formula is working great...Is there now a way to call that value
returned from that cell within a macro

for instance

Rows (3:"Value of cell").select

Thanks

Automatically Run a macro when a worksheet is selected
 

"Stephen Hartman" wrote in message
...
Rob, the formula is working great...Is there now a way to call that value
returned from that cell within a macro

for instance

Rows (3:"Value of cell").select

Hi Stephen,

In that instance it would be easier to just code it directly, rather
than going through an intermediate function:

Sheet1.UsedRange.Resize(, 1).EntireRow.Select

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *

Automatically Run a macro when a worksheet is selected
 

That is perfect!

Thanks for all your help...

sh