Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 * |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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?) |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 * |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automatically Run a macro when a worksheet is selected
That is perfect!
Thanks for all your help... sh |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to run specific macro on selected worksheet? | Excel Discussion (Misc queries) | |||
Automatically copy selected information from one worksheet to anot | Excel Discussion (Misc queries) | |||
Macro to Sort automatically when file/save is selected | Excel Discussion (Misc queries) | |||
How to run a macro when a worksheet is selected | Excel Worksheet Functions | |||
Automatically Run a macro when a worksheet is selected | Excel Programming |