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
