Selecting multiple worksheets

Delton

Board Regular
Joined
Oct 19, 2002
Messages
66
Hello,

I am searching for a code to select multiple worksheets. The worksheet selection is from the fourth sheet in the workbook, named “SUMMARY”, to the last sheet in the workbook. The last sheet and its name will continually change, and the number of sheets will vary as well.

The constant is the “Summary” sheet name and its position, and the need to range from this Summary sheet to the end sheet whatever it may be named. All sheets in the selection are adjacent.

I would like to use this procedure to edit data in the selected identical sheets as well as to select them, in another separate routine, for printing them all. Any thoughts will be appreciated. Thank you.

Delton
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
You can probably use something like this for the first part:

<font face=Tahoma><SPAN style="color:#00007F">Sub</SPAN> DoSomething()
    <SPAN style="color:#00007F">Dim</SPAN> ws <SPAN style="color:#00007F">As</SPAN> Worksheet
        <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> ws <SPAN style="color:#00007F">In</SPAN> ActiveWorkbook.Worksheets
            <SPAN style="color:#00007F">If</SPAN> ws.Name <> "Summary" <SPAN style="color:#00007F">Then</SPAN>
                ws.Activate
                <SPAN style="color:#007F00">'   Do stuff here</SPAN>
                <SPAN style="color:#007F00">'   Note that it's not always necessary to activate the sheet</SPAN>
                <SPAN style="color:#007F00">'   to do stuff</SPAN>
            <SPAN style="color:#00007F">Next</SPAN> ws
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

As for the second part, see: http://j-walk.com/ss/excel/tips/tip48.htm

HTH,

Smitty
 

Zack Barresse

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web
Hi,

I see Smitty has already gotten you an excellent solution. (Hiya Smitty!!) Just wanted to make a note about the worksheet (ws) iteration and referring to that object. You don't need ..
Code:
ws.Activate
.. as you are referring to the object (ws) already. You just need to preceed each sheet specific object with the "ws.". So if you were referring to Range("A1"), you'd need ..
Code:
ws.Range("A1").Value = "my value"
Or, if you wanted to use a With statement, you could use ..
Code:
With ws
    .Range("A1").Value = "my value"
    .Range("A2").Value = Format(Date, "mmmm yyyy")
End With
Using this type of method, you just need to remember to preceed the range object with the period ( . ) character as it refers to the object in the With statement.

Sorry to intrude, not trying to hijack the thread. Just fyi, fwiw.
 

Delton

Board Regular
Joined
Oct 19, 2002
Messages
66
Thanks Smitty.

I was a little unclear in the information I provided, as usual. Your solution of course works fine, but what I nelected to clarify was that the first three sheets are not to be included. The <> of course then only excludes the Summary sheet. The first four constant sheets are Help, Index, CoverSheet, and Summary. I would like to exclude the first three from the range.

I have a place for the code you gave though, so your time and effort served me well in spite of my apparent error in clarity and is appreciated.

Delton
 

Todd Bardoni

Well-known Member
Joined
Aug 29, 2002
Messages
3,042

ADVERTISEMENT

If ws.Name <> "Help" Or ws.Name <> "Summary" Or ws.Name <> "CoverSheet" Then
 

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
Heya Zack, no worries. I used that method because without the second "ws", I've had it bomb on some PC's. (Not a clue as to why though...)

Smitty

(Sup Todd!)
 

Zack Barresse

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web
Another option would be something like ...

Code:
Dim i As Long
For i = 4 To Sheets.Count
    Sheets(i). ...
Next i
 

Forum statistics

Threads
1,147,846
Messages
5,743,521
Members
423,801
Latest member
paulj4177

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