Hi Paul,
Thanks for the detailed info. Not too wordy at all. I hadn't realised that
there were 2 different syntaxes (syntii?) - Sheets(1) and Sheet1. That makes
sense now.
So then Sheets(1).Name will always return something, (though not always what
you'd expect) because it is just the first sheet in the sheets array. But
Sheet1.Name may produce an error, because Sheet1 may have been deleted.
The code line that I quoted (Sheets(Sheets.Count).Select) always works because
it is using the sheets array, not the sheet name or the sheet object.
Thanks again. All very valuable knowledge.
Regards - Dave.
Date: Tue, 15 Feb 2011 05:00:59 -0800
From: schreiner_p...@att.net
Subject: Re: $$Excel-Macros$$ A little OT
To: excel-macros@googlegroups.com
The biggest confusion here is the difference between
the Worksheet OBJECT: Sheet1
the Worksheet NAME: Sheet1
and Sheets ARRAY: Sheets(1)
An Excel workbook must contain at least one sheet.
Excel will INTERNALLY "Identify" this sheet object as "Sheet1"
and will initially NAME the sheet "Sheet1"
But these are two different things.
In the VB Editor Object Explorer, you'll see:
Sheet1(Sheet1)
Meaning that the Sheet1 object is NAMED "Sheet1"
The key here is that Sheet1 can be NAMED anything,
but will always be Sheet1 as long as it exists.
You can name your dog "kitty" but that doesn't change the fact that it's still
a dog.
That's why you can use:
Sheet1.Name = "DataSheet"
The Name Property of the Sheet1 Object is set to "DataSheet"
So, even though the VB Object Explorer shows:
Sheet1(DataSheet)
You cannot use:
DataSheet.Select
Because "DataSheet" is not an Object...
Now, the Sheets() Array...
It's exactly that.
It's an array of sheet objects.
The interesting thing here is that the position in the array is
dependent upon where the sheet appears in the workbook.
If you have three sheets, Named: "Name1", "Name2" and "Name3"
and they were originally created in numerical order and positioned
left-to-right.
The Sheets() Array will have them listed as:
Sheets(1).Name = "Name1"
Sheets(2).Name = "Name2"
Sheets(3).Name = "Name3"
and the VBA Object Explorer will show them as:
Sheet1(Name1)
Sheet2(Name2)
Sheet3(Name3)
But if you move Name3 to the far left, so they're shown as:
"Name3", "Name1", "Name2"
The Object Explorer will still show them as:
Sheet1(Name1)
Sheet2(Name2)
Sheet3(Name3)
But the Sheets ARRAY will be:
Sheets(1).Name = "Name3"
Sheets(2).Name = "Name1"
Sheets(3).Name = "Name2"
BTW: The Sheets Array also has the "internal" name as the "CodeName" object.
So.. summarize...
There are three distinct ways to identify a sheet (maybe more?)
The Sheet OBJECT,
the Sheet NAME, and
the Sheets ARRAY
You cannot change the "codeName" of an existing sheet object.
You CAN (of course) change the Sheet Name,
and the Sheets ARRAY is changed by the position of the sheet in the workbook.
In answer to your question.. Your question contains syntax errors.
Because when you say there are 7 sheets, but Sheet(7) doesn't exist.
You mean that Sheet7 doesn't exist... Sheets(7) is the 7th element of the Sheets
ARRAY, which DOES exist.
Does that help? or was it so "wordy" that you fell asleep midway and woke up
with a keyboard imprint on your right cheek?
Paul
From: Dave Bonallack <davebonall...@hotmail.com>
To: "excel-macros@googlegroups.com" <excel-macros@googlegroups.com>
Sent: Mon, February 14, 2011 11:33:24 PM
Subject: $$Excel-Macros$$ A little OT
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
--
----------------------------------------------------------------------------------
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
