I have an excel file with multiple tabs. I want to have a summary tab at the beginning that would list the last value in each tab like so:

Sheet2 last value= xxxxxx
Sheet3 last value= xxxxxx
Sheet3 last value= xxxxxx

I found the function to find the REAL last value here:

http://www.beyondtechnology.com/geeks012.shtml

Then it says the LastCell function shown here would not be used in a worksheet, but would be called from another VBA procedure:

Sub Demo ()

MsgBox LastCell(Sheet1).Row

End Sub


I'm a VBA newbie, so I'm stuck at this point. How do I get everything to work together in Excel?

While attempting to educate myself, I read that you need to have the file open that it was created in for things to work properly. Then I found that if you wanted it to work while the file was closed, you had to create an Excel add-in.

In summary, I want to be able to create a new tab at any point in any file and be able to list the last cell value of mulitple tabs (in the same file) in summary form in the new tab that I inserted. I want to be able to do it without needing another file open or using the file's name that the UDF was created in.

Here's an example file of what I'm looking to accomplish:

http://www.savefile.com/files/9280863


BTW, I'm using Excel 2000.