Need to get the last worksheet name in msgbox

[DivyaS55]

Hi,
I need to get the last (rightmost worksheet) name in msgbox. I used Sheets(Sheets.Count) to get last sheet. But its only giving first sheets name. Kindly help me on this. Here is my code.

Sub ShowMRNumber()
    Dim xlApp As Excel.Application
    Dim xlBook As Excel.Workbook
    Dim xlSheet As Excel.Worksheet
 
    Set xlApp = New Excel.Application
    Set xlBook = xlApp.Workbooks.Open("location")
    Set xlSheet = xlApp.Sheets(Sheets.Count)
 
        MsgBox "MR No. is" & vbNewLine xlSheet.Name
 
xlApp.Workbooks.Close
 
End Sub

[MikeTheBike]

Hi

Apart from the syntax error in the MsgBox statement, I currently cannot see why this does not result in the last (Right) sheet name being returned.

If you get the first sheet name using this, what do get if you use xlApp.Sheets(1).Name ?


MTB

[NeoPa]

In your line #8 you have two references to Sheets. Both seem incorrect to me.

One is listed as a property of the application and the other has no object reference at all.

I would expect to see :

    Set xlSheet = xlBook.Sheets(xlBook.Sheets.Count)