Thanks for your thorough explanation Paul. I now understand that sheets() points to the position of the sheet in the file. I still do not understand how to use the sheet.name though. This is what I have in objext explorer
sheet1 (Process) 'is the first sheet sheet2 (Hours) ' is 3rd sheet sheet3 (Breakdown) ' is 2nd sheet 1- Should I say : Thisworkbook.sheet2.name= "whatever" Thisworkbook.sheet("whatever").select 2- Doesn't this change the name that the tab is showing (showing "whatever" instead of "hours")? If so, what if somebody renames the tabs later. Will this not confuse a user as I am replacing the sheet name to what I want? 3- Or should I simply say: Thisworkbook.sheet2.select? Does this select sheet2(hours) or sheet2(Breakdown)? I guess it will be sheet2(hours). 4- What if I need to use variable and refer to sheet2 not sheet in 2nd position? I used to used sheets(i). if i=2 then it will point to sheet3(which in 2nd position-as I learnt from you) whereas I need to point to sheet2 which is in 3rd position. what syntax should I use? 5- Here is the part that I am referencing my sheets: Dim WSB, WSH As Worksheet Dim u, t, p, q, LastRowH, LastRowB As Integer Set WSB = ThisWorkbook.Sheets(9) ' : I changed it to Set WBS = Thisworkbook.Sheet9 and it does not work Set WSH = ThisWorkbook.Sheets(2) I am still so confused. I appreciate if you could explaine this a bit more. Best regards, Nasim On Feb 14, 10:07 pm, Paul Schreiner <schreiner_p...@att.net> wrote: > 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 athttp://www.excel-macros.blogspot.com > 4. Learn VBA Macros athttp://www.quickvba.blogspot.com > 5. Excel Tips and Tricks athttp://exceldailytip.blogspot.com > > To post to this group, send email to excel-macros@googlegroups.com > > <><><><><><><><><><><><><><><><><><><><><><> > Like our page on facebook , Just follow below > linkhttp://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