Hi Paul, Thanks for your help. I think I now understand how it works. After I sent you the file I tried what is suggested in below link one more time and it works great. It is basically the same thing that you told me to do with a little bit of change.
Here is the link for anybody interested: http://www.tek-tips.com/faqs.cfm?fid=4090 So I changed the sheet's (name) in the properties window to "shtBonus" and then used it like this: shtBonus.Range("A1").select Once again, Thanks for your help and directing me to right direction. Best regards, Nasim On Feb 15, 11:48 am, Paul Schreiner <schreiner_p...@att.net> wrote: > The choice of which approch to use is really a question of Readability and > Functionality. > > You can use: > Sheet1.Select > Sheet2.Select > Sheet3.Select > > as long as you know you're not going to be deleting and adding sheets. > Once you delete a sheet, even if you make a copy and delete the original, this > will > fail and you have to update the code. > Plus, as a programmer, you'll have to remember which sheet is the "Process" > sheet, etc. > > The same problem occurs with the use of Sheets(i) > If someone moves a sheet within the workbook, the sheets() array changes. > > I prefer to use something like this at the beginning of the macro: > ShtProcess = "Process" > ShtHours = "Hours" > ShtBreakdn = "Breakdown" > > then within the macro: > Sheets(ShtProcess).Select > That way, If I want, I can create a routine that checks for valid sheet names. > If someone changes the sheet name, I can find it based on keywords and rename > the sheet variable. > Even then, if I decide to change the sheet name, I only have to change it once > in my routine. > > also, see my notes below: > > > > ________________________________ > > From: Nasim <nbeiz...@gmail.com> > To: MS EXCEL AND VBA MACROS <excel-macros@googlegroups.com> > Sent: Tue, February 15, 2011 11:37:11 AM > Subject: Re: $$Excel-Macros$$ Sheets(2) refers to 2nd sheet in excell file not > sheet with index2 > > ---------------------------------------------------------- > this should be: > Thisworkbook.sheetS("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? > ----------------------------------------------------------------- > Yes... Thisworkbook.sheet2.name = "whatever" does change the sheet name > that is displayed in the sheet "tabs". > But you wouldn't change the sheet name unless necessary... > Did I misunderstand the question? > ----------------------------------------------------------------------- > > 3- Or should I simply say: Thisworkbook.sheet2.select? Does this > select sheet2(hours) or sheet2(Breakdown)? I guess it will be > sheet2(hours). > ----------------------------------------------------------------- > First of all sheet2 is not an array (so no ()'s) > Sheet2 is a sheet OBJECT. > The Sheets() array requires an array index, or the sheet name. > sheets(Breakdown) would imply that there is a variable called Breakdown > that has a value being passed to the Sheets() array. > Since Breakdown is NOT a variable, it would result in: > Sheets(), which would fail. > Using your example, you can use: > Sheets("Breakdown").select > Sheets(2).Select (since you said "Breakdown" is the second sheet) > Sheet3.select > --------------------------------------------------------------------- > > 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? > ---------------------------------------------------------------------- > I would suggest using the sheet name: > ShtName = "Breakdown" > Sheets(ShtName). > If you MUST use the sheets array index, > you can find it by using: > For i = 1 to sheets.count > if (sheets(i).name = "Breakdown") then > exit for > end if > next i > sheets(i).Select > ---------------------------------------------------------------------- > > 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) > > ---------------------------------------------------------------------- > Can you tell me what your workbook structure is? > > what are the sheet names? > what does the VB explorer show? > > can you send me the file? (or at least part of it?) > > I'd be glad to help > > Paul > ------------------------------------------------------------------------- > > 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 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 > > 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 -- ---------------------------------------------------------------------------------- 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
