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
