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
