none
Need to get the last worksheet name in msgbox RRS feed

  • Question

  • 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
    Friday, May 19, 2017 9:20 AM

All replies

  • Change

    Set xlSheet = xlApp.Sheets(Sheets.Count)

    to

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


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Friday, May 19, 2017 11:43 AM