By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,961 Members | 2,492 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,961 IT Pros & Developers. It's quick & easy.

Need to get the last worksheet name in msgbox

P: 1
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.
Expand|Select|Wrap|Line Numbers
  1. Sub ShowMRNumber()
  2.     Dim xlApp As Excel.Application
  3.     Dim xlBook As Excel.Workbook
  4.     Dim xlSheet As Excel.Worksheet
  5.  
  6.     Set xlApp = New Excel.Application
  7.     Set xlBook = xlApp.Workbooks.Open("location")
  8.     Set xlSheet = xlApp.Sheets(Sheets.Count)
  9.  
  10.         MsgBox "MR No. is" & vbNewLine xlSheet.Name
  11.  
  12. xlApp.Workbooks.Close
  13.  
  14. End Sub
May 19 '17 #1

✓ answered by 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 :
Expand|Select|Wrap|Line Numbers
  1.     Set xlSheet = xlBook.Sheets(xlBook.Sheets.Count)

Share this Question
Share on Google+
2 Replies


Expert 100+
P: 634
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
May 19 '17 #2

NeoPa
Expert Mod 15k+
P: 31,492
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 :
Expand|Select|Wrap|Line Numbers
  1.     Set xlSheet = xlBook.Sheets(xlBook.Sheets.Count)
May 19 '17 #3

Post your reply

Sign in to post your reply or Sign up for a free account.