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 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
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
