The biggest confusion here is the difference between the Worksheet OBJECT: Sheet1 the Worksheet NAME: Sheet1 and Sheets ARRAY: Sheets(1)
An Excel workbook must contain at least one sheet. Excel will INTERNALLY "Identify" this sheet object as "Sheet1" and will initially NAME the sheet "Sheet1" But these are two different things. In the VB Editor Object Explorer, you'll see: Sheet1(Sheet1) Meaning that the Sheet1 object is NAMED "Sheet1" The key here is that Sheet1 can be NAMED anything, but will always be Sheet1 as long as it exists. You can name your dog "kitty" but that doesn't change the fact that it's still a dog. That's why you can use: Sheet1.Name = "DataSheet" The Name Property of the Sheet1 Object is set to "DataSheet" So, even though the VB Object Explorer shows: Sheet1(DataSheet) You cannot use: DataSheet.Select Because "DataSheet" is not an Object... Now, the Sheets() Array... It's exactly that. It's an array of sheet objects. The interesting thing here is that the position in the array is dependent upon where the sheet appears in the workbook. If you have three sheets, Named: "Name1", "Name2" and "Name3" and they were originally created in numerical order and positioned left-to-right. The Sheets() Array will have them listed as: Sheets(1).Name = "Name1" Sheets(2).Name = "Name2" Sheets(3).Name = "Name3" and the VBA Object Explorer will show them as: Sheet1(Name1) Sheet2(Name2) Sheet3(Name3) But if you move Name3 to the far left, so they're shown as: "Name3", "Name1", "Name2" The Object Explorer will still show them as: Sheet1(Name1) Sheet2(Name2) Sheet3(Name3) But the Sheets ARRAY will be: Sheets(1).Name = "Name3" Sheets(2).Name = "Name1" Sheets(3).Name = "Name2" BTW: The Sheets Array also has the "internal" name as the "CodeName" object. So.. summarize... There are three distinct ways to identify a sheet (maybe more?) The Sheet OBJECT, the Sheet NAME, and the Sheets ARRAY You cannot change the "codeName" of an existing sheet object. You CAN (of course) change the Sheet Name, and the Sheets ARRAY is changed by the position of the sheet in the workbook. In answer to your question.. Your question contains syntax errors. Because when you say there are 7 sheets, but Sheet(7) doesn't exist. You mean that Sheet7 doesn't exist... Sheets(7) is the 7th element of the Sheets ARRAY, which DOES exist. Does that help? or was it so "wordy" that you fell asleep midway and woke up with a keyboard imprint on your right cheek? Paul ________________________________ From: Dave Bonallack <davebonall...@hotmail.com> To: "excel-macros@googlegroups.com" <excel-macros@googlegroups.com> Sent: Mon, February 14, 2011 11:33:24 PM Subject: $$Excel-Macros$$ A little OT 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 -- ---------------------------------------------------------------------------------- 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