Selecting Specific Sheets

[Groff.Michael]

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?

 

[Guest]

This should be close...

Dim lng As Long

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

 

[tylagusim]

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 /

 

[Groff.Michael]

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

Thank you both!

 

[Guest]

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.

 

[Groff.Michael]

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

 

[tylagusim]

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

/ Tyla /