I have the following code:

Sheets(Array("Bridal", "Executive", "Facilities", "Finance",
"Furniture", "Housewares", _
"Human Resources", "Human Resources", "MIO", "Planning", "Real
Estate", "Retail Technology", _
"Stores Merch", "Tabletop", "Textiles", "Visual")).Select

How can I simplify this so that I select the third sheet in the file to
the last sheet in the file?

THanks

I'd do something like:

Option Explicit
Sub testme()
Dim iCtr As Long
Dim myLimit As Long

myLimit = 2
If Sheets.Count myLimit Then
For iCtr = myLimit To Sheets.Count
Sheets(iCtr).Select Replace:=CBool(iCtr = (myLimit + 1))
Next iCtr
End If
End Sub



snax500 wrote:

I have the following code:

Sheets(Array("Bridal", "Executive", "Facilities", "Finance",
"Furniture", "Housewares", _
"Human Resources", "Human Resources", "MIO", "Planning", "Real
Estate", "Retail Technology", _
"Stores Merch", "Tabletop", "Textiles", "Visual")).Select

How can I simplify this so that I select the third sheet in the file to
the last sheet in the file?

THanks

--

Dave Peterson

Dave, I had to study what you were doing at first, but the portion beginning
"Replace..." of this line

Sheets(iCtr).Select Replace:=CBool(iCtr = (myLimit + 1))

was a work of art!

Bill
"Dave Peterson" wrote in message
...
I'd do something like:

Option Explicit
Sub testme()
Dim iCtr As Long
Dim myLimit As Long

myLimit = 2
If Sheets.Count myLimit Then
For iCtr = myLimit To Sheets.Count
Sheets(iCtr).Select Replace:=CBool(iCtr = (myLimit + 1))
Next iCtr
End If
End Sub



snax500 wrote:

I have the following code:

Sheets(Array("Bridal", "Executive", "Facilities", "Finance",
"Furniture", "Housewares", _
"Human Resources", "Human Resources", "MIO", "Planning", "Real
Estate", "Retail Technology", _
"Stores Merch", "Tabletop", "Textiles", "Visual")).Select

How can I simplify this so that I select the third sheet in the file to
the last sheet in the file?

THanks

--

Dave Peterson

It is very nice. I forgot who I stole, er, borrowed it from!

William Benson wrote:

Dave, I had to study what you were doing at first, but the portion beginning
"Replace..." of this line

Sheets(iCtr).Select Replace:=CBool(iCtr = (myLimit + 1))

was a work of art!

Bill
"Dave Peterson" wrote in message
...
I'd do something like:

Option Explicit
Sub testme()
Dim iCtr As Long
Dim myLimit As Long

myLimit = 2
If Sheets.Count myLimit Then
For iCtr = myLimit To Sheets.Count
Sheets(iCtr).Select Replace:=CBool(iCtr = (myLimit + 1))
Next iCtr
End If
End Sub



snax500 wrote:

I have the following code:

Sheets(Array("Bridal", "Executive", "Facilities", "Finance",
"Furniture", "Housewares", _
"Human Resources", "Human Resources", "MIO", "Planning", "Real
Estate", "Retail Technology", _
"Stores Merch", "Tabletop", "Textiles", "Visual")).Select

How can I simplify this so that I select the third sheet in the file to
the last sheet in the file?

THanks

--

Dave Peterson

--

Dave Peterson

Jake Marx was the first person I saw use it, but wouldn't doubt that J.E.
McGimsey would have used something like that. He likes to pack as much
action as possible in a line of code.

--
Regards,
Tom Ogilvy


"Dave Peterson" wrote in message
...
It is very nice. I forgot who I stole, er, borrowed it from!

William Benson wrote:

Dave, I had to study what you were doing at first, but the portion
beginning
"Replace..." of this line

Sheets(iCtr).Select Replace:=CBool(iCtr = (myLimit + 1))

was a work of art!

Bill
"Dave Peterson" wrote in message
...
I'd do something like:

Option Explicit
Sub testme()
Dim iCtr As Long
Dim myLimit As Long

myLimit = 2
If Sheets.Count myLimit Then
For iCtr = myLimit To Sheets.Count
Sheets(iCtr).Select Replace:=CBool(iCtr = (myLimit + 1))
Next iCtr
End If
End Sub



snax500 wrote:

I have the following code:

Sheets(Array("Bridal", "Executive", "Facilities", "Finance",
"Furniture", "Housewares", _
"Human Resources", "Human Resources", "MIO", "Planning", "Real
Estate", "Retail Technology", _
"Stores Merch", "Tabletop", "Textiles", "Visual")).Select

How can I simplify this so that I select the third sheet in the file
to
the last sheet in the file?

THanks

--

Dave Peterson

--

Dave Peterson

It really is. I wrote to Dave for an explanation. Here it is...



Take a look at help for .select.

It has an option to replace the selected sheet or keep adding.

False means that it's just "added" to the current group of selected
sheets.

True means to select that sheet and "unselect" the previous sheets.

I want True only on the first worksheet in that group--when (ictr =
mylimit
+ 1)

mylimit = 2

Then ictr = 3 (the first of the group).

The next time through, ictr will be 4
4 is different from mylimit + 1 (=3) so it evaluates to False--and so
that
second (and all subsequent sheets get added to the group.


Thanks again Dave.

The magic is not knowing what TRUE/FALSE does as an argument to the Select
method, but it is the elegant way of not having to set up if ... else ....
end if clause to separate treatment. I am going to be on the lookout for
opportunities to do that when an T/F argument varies under two mutually
exclusive conditions like shown ... i.e., something either IS or IS NOT =
Mylimit.

Beautiful.



"snax500" wrote in message
oups.com...
It really is. I wrote to Dave for an explanation. Here it is...



Take a look at help for .select.

It has an option to replace the selected sheet or keep adding.

False means that it's just "added" to the current group of selected
sheets.

True means to select that sheet and "unselect" the previous sheets.

I want True only on the first worksheet in that group--when (ictr =
mylimit
+ 1)

mylimit = 2

Then ictr = 3 (the first of the group).

The next time through, ictr will be 4
4 is different from mylimit + 1 (=3) so it evaluates to False--and so
that
second (and all subsequent sheets get added to the group.


Thanks again Dave.