Choose multiple sheets in range

[Drmwvr7266]

I have sheets that are laid out in both portrait and landscape. By dumb luck I suppose, all sheets from #1 to #34 are landscape, and all sheets thereafter will be Portrait. This requires some thought for printing. However, I just need a small snippet of code to complete the code.

If activesheet is between sheet1 and sheet34 Then
Macro1
Else
Macro 2
End If.

How do I select this range, and if I should need to modify it, how would i change it? Change it meaning:

If active sheet is sheet 1,2, 3, 6, 7, 10 Then
Macro1
Else
Macro2
End If


Thanx in advance. This site has been as good as a second education! Who needs college anymore when we have the INTERNET!

 

[Andrew Poulsom]

Possibly the best way to maintain flexibilty is to use the Select Case structure, like this:

Sub Test()
   Dim Sh as Worksheet
   Dim x As Integer
   x = 1
   For Each Sh in ThisWorkbook.Worksheets
      Select Case x
         Case 1 To 34
'        *** Whatever ***
         Case Else
'        *** Whatever ***
      End Select
      x = x + 1
   Next Sh
End Sub

The Case statement is quite flexible, eg:

Case 1 To 4, 7 To 9, 11, 13

and can even use strings, eg:

Case "nuts" To "soup"

matches strings that fall between nuts and soup in alphabetic order. So in the above code you could even use the sheet name, eg:

      Select Case Sh.Name
         Case "Sheet1" To "Sheet34"
'        *** Whatever ***
         Case Else
'        *** Whatever ***
      End Select

Look in help for more info.

 

[Drmwvr7266]

Okay, your idea works. But Maybe I need to be a bit clearer. The intention of this is so that when a PRINT commandbutton is clicked, the code can tell which sheet is active (Aka sheet1, sheet2, etc) and chose which code will run.

If the sheets are between 1 and 33 (Though their names are all across the spectrum), clicking the button will run Macro1. However, if the activesheet is sheet34 or above, it will choses macro2.

The reasoning is that some sheets have more cells, arranged differently than do others. My print button is currently set to highlight and print A:H. But some of these pages(Sheets 34 and above), the widths of the cells changes so that A:Y may be printed, for example. Hope this clarifies some things.
This message was edited by Drmwvr7266 on 2002-09-17 11:42

 

[Drmwvr7266]

Problem resolved. I just gave the user the option of portrait or landscape with examples. Thanks for the help.