I am trying to have all but two sheets selected out of a varying
number of possible sheets. The two that I don't want selected are
Sheet1 and the very last one (SheetX for example).

So far I have used this code below, but it only gets me half way there
(doesn't select the first sheet, but still has the last one selected
with the rest of the group).

Dim shCount As Integer

ThisWorkbook.Worksheets(2).Select
For shCount = 3 To ThisWorkbook.Worksheets.Count
Worksheets(shCount).Select (False)
Next shCount
For Each ws In Worksheets
Next ws

Any other ideas?

This should be close...

Dim lng As Long

Sheets(2).Select
For lng = 2 To Sheets.Count - 1
Sheets(lng).Select False
Next lng

--
HTH...

Jim Thomlinson


" wrote:

I am trying to have all but two sheets selected out of a varying
number of possible sheets. The two that I don't want selected are
Sheet1 and the very last one (SheetX for example).

So far I have used this code below, but it only gets me half way there
(doesn't select the first sheet, but still has the last one selected
with the rest of the group).

Dim shCount As Integer

ThisWorkbook.Worksheets(2).Select
For shCount = 3 To ThisWorkbook.Worksheets.Count
Worksheets(shCount).Select (False)
Next shCount
For Each ws In Worksheets
Next ws

Any other ideas?

I suspect a one-line would do the job. Try this:

Sheets(Array(Sheets(1).Name, Sheets(Worksheets.Count).Name)).Select

The definition of "first" and "very last" can get odd in Excel. This
code picks the left-most and right-most tabs within the workbook.
HTH

/ Tyla /



On Feb 28, 11:12 am, wrote:
I am trying to have all but two sheets selected out of a varying
number of possible sheets. The two that I don't want selected are
Sheet1 and the very last one (SheetX for example).

So far I have used this code below, but it only gets me half way there
(doesn't select the first sheet, but still has the last one selected
with the rest of the group).

Dim shCount As Integer

ThisWorkbook.Worksheets(2).Select
For shCount = 3 To ThisWorkbook.Worksheets.Count
Worksheets(shCount).Select (False)
Next shCount
For Each ws In Worksheets
Next ws

Any other ideas?

The first one worked well, but I'll hang on to both in case something
should arise.

Thank you both!

I think you have the request backwards... As per the OP..."The two that I
don't want selected are Sheet1 and the very last one"... Those are the only
two sheets your code selects.
--
HTH...

Jim Thomlinson


" wrote:

I suspect a one-line would do the job. Try this:

Sheets(Array(Sheets(1).Name, Sheets(Worksheets.Count).Name)).Select

The definition of "first" and "very last" can get odd in Excel. This
code picks the left-most and right-most tabs within the workbook.
HTH

/ Tyla /



On Feb 28, 11:12 am, wrote:
I am trying to have all but two sheets selected out of a varying
number of possible sheets. The two that I don't want selected are
Sheet1 and the very last one (SheetX for example).

So far I have used this code below, but it only gets me half way there
(doesn't select the first sheet, but still has the last one selected
with the rest of the group).

Dim shCount As Integer

ThisWorkbook.Worksheets(2).Select
For shCount = 3 To ThisWorkbook.Worksheets.Count
Worksheets(shCount).Select (False)
Next shCount
For Each ws In Worksheets
Next ws

Any other ideas?

Jim - The code you provided seems to work well.
..
..
..
An extension of my initial question. I've noticed that when I go to
print the selected sheets my formatting only sticks for the first page
and then is lost after that. Can I set up my code similar to this
(obviously it does not work)?
Thanks!!


Dim lng As Long
Sheets(2).Select
For lng = 2 To Shets.Count - 1
Sheets(lng).Select False
With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.LeftMargin = Application.InchesToPoints(0.45)
.RightMargin = Application.InchesToPoints(0.45)
.Orientation = xlPortrait
.PaperSize = xlPaperLegal
.FitToPagesWide = 1
.FitToPagesTall = 1
End With
Next lng

Right you are (Memo to self: New glasses immediately!).

/ Tyla /





On Feb 28, 11:38 am, Jim Thomlinson <James_Thomlin...@owfg-Re-Move-
This-.com wrote:
I think you have the request backwards... As per the OP..."The two that I
don't want selected are Sheet1 and the very last one"... Those are the only
two sheets your code selects.
--
HTH...

Jim Thomlinson

" wrote:
I suspect a one-line would do the job. Try this:

Sheets(Array(Sheets(1).Name, Sheets(Worksheets.Count).Name)).Select

The definition of "first" and "very last" can get odd inExcel. This
code picks the left-most and right-most tabs within the workbook.
HTH

/Tyla/

On Feb 28, 11:12 am, wrote:
I am trying to have all but two sheets selected out of a varying
number of possible sheets. The two that I don't want selected are
Sheet1 and the very last one (SheetX for example).

So far I have used this code below, but it only gets me half way there
(doesn't select the first sheet, but still has the last one selected
with the rest of the group).

Dim shCount As Integer

ThisWorkbook.Worksheets(2).Select
For shCount = 3 To ThisWorkbook.Worksheets.Count
Worksheets(shCount).Select (False)
Next shCount
For Each ws In Worksheets
Next ws

Any other ideas?