Hi Paul, I have also noticed that after deleting and creating sheets, the Sheet numbers are out of order, and can be missing altogether. ie in a workbook of 7 sheets, one of them may be Sheet(11), while sheets 8 and 9 are not present at all. So, my question is, why does this line of code (Sheets(Sheets.Count).Select) always select the last sheet in a workbook, even when the last sheet isn't necessarily the one with the highest sheet number, and also, if the sheet.count is say, 7, the thing still works if Sheet(7) doesn't exist! Regards - Dave
Date: Mon, 14 Feb 2011 19:07:28 -0800 From: schreiner_p...@att.net Subject: Re: $$Excel-Macros$$ Sheets(2) refers to 2nd sheet in excell file not sheet with index2 To: excel-macros@googlegroups.com In your VBA Object Explorer, you'll see Sheet1(Name1) Sheet2(Name2) Sheet3(Name3) If you delete the sheet named "Name2", create a new sheet, and call the NEW sheet "Name2" It won't be: Sheet2(Name2), it'll be: Sheet4(Name2). So the LIST will be: Sheet1(Name1) Sheet3(Name3) Sheet4(Name4) in VBA the Sheets array is: Sheets(1).Name = "Name1" Sheets(2).Name = "Name3" Sheets(3).Name = "Name2" Because the Sheets() array is a list of sheet names. An odd occurence though is: If you save and exit Excel, open the file, and create a new Sheet (Name4) it will create it as: Sheet2(Name4) and the array becomes: Sheets(1).Name = "Name1" Sheets(2).Name = "Name4" Sheets(3).Name = "Name3" Sheets(4).Name = "Name2" So, basically, when you see "sheet1", "sheet2", etc in the VBA Project Explorer, it is NOT a sheet name and canNOT be used as the array. Because of this, using sheets(1) or Sheets(2) is inconsistent, and I NEVER use it. (some of my macros delete and create several sheets during processing. It's not uncommon to be up to sheet300 in a session). What I would suggest is to use: Sheets("Name1") or set a variable for the sheet name ShtName = "Name3" and use: Sheets(ShtName).Select Paul From: Nasim <nbeiz...@gmail.com> To: MS EXCEL AND VBA MACROS <excel-macros@googlegroups.com> Sent: Mon, February 14, 2011 6:02:46 PM Subject: $$Excel-Macros$$ Sheets(2) refers to 2nd sheet in excell file not sheet with index2 Hi, we are using excel 2010. I use sheets(2) in my code and it was working fine (since this was the 2nd tab in my file till I manually added another sheet and I moved this 3rd sheet betwenn sheet 1 and 2 so now 3rd sheet is in 2nd order in my file(2nd tab). I have renamed the tabs ofcourse and the indexes should still work but they dont. here is the broblem set wsh = thisworkbook.sheets(2) wsh.activate : this line activates sheets(3) which is the 2nd tab in my file now I used sheets("sheet2") and it produces errors. It seems like all sheet indexes after have shifted 1 number up. I need to reference many different sheets in this file and I want to be able to add/delete/move/rename sheets without any problems. Only if index was working .... I have never had this problem before. this is the first time this is happening. Is it seemthing I do wrong or is it the setup or is it excel 2010? I appreciate your help. Best regards, Nasim -- ---------------------------------------------------------------------------------- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- ---------------------------------------------------------------------------------- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- ---------------------------------------------------------------------------------- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel