Choose multiple sheets in range

Drmwvr7266

Board Regular
Joined
Apr 7, 2002
Messages
164
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!
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Possibly the best way to maintain flexibilty is to use the Select Case structure, like this:

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

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

Look in help for more info.
 

Drmwvr7266

Board Regular
Joined
Apr 7, 2002
Messages
164
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

Board Regular
Joined
Apr 7, 2002
Messages
164
Problem resolved. I just gave the user the option of portrait or landscape with examples. Thanks for the help.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,491
Messages
5,596,460
Members
414,069
Latest member
StudExcel

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top