Last row in a particular sheet / workbook
I assume from you example the the Sheet is the SheetName. You need to pas
something like this
"[book1.xls]Sheet1"
You could do this
set WB1 = workbooks("Book1.xls")
LastRow = Get_LastRow("[" & WB1.name & "]" & "Sheet1")
or
set WB1 = workbooks("Book1.xls")
set sht = WB1.sheets("Sheet1")
LastRow = Get_LastRow("[" sht.parent.name & "]" & sht.name)
"Michael R" wrote:
I have a UDF to return the last row in a particular sheet:
Function Get_LastRow(Sheet As String) As Long
Application.Volatile True
Get_LastRow = Sheets(Sheet).UsedRange.Rows.Count
End Function
Now here is the problem:
* This UDF is stored in a module in workbook WB1.
* WB1 Sheet1 has 10 rows. WB1 Sheet2 has 9 rows. I call the UDF from WB1
Sheet3 twice to return the last rows of WB1 Sheet1 (10) and WB1 Sheet2 (9).
* At the same time I have workbook WB2 open. WB2 Sheet1 has 6 rows, WB2
Sheet2 has 3 rows. WB2 uses some results from WB1. WB2 has the focus - it is
displayed on top.
* When I do a Full Recalculate (F9) with WB2 on top, then switch to WB1 I
find that the UDF has put 6 and 3 in WB1. I have tried renaming Sheet1 and
Sheet2 in WB2 to something else, then the recalculate results in #VALUE in
WB1.
(When I recalculate with WB1 on top I receive the expected results 10 and 9.)
How can I get the last row of the sheet from the workbook that calls the UDF?
Thanks for your help!
|