Selecting multiple worksheets

[Delton]

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

 

[Smitty]

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]

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 ..

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 ..

ws.Range("A1").Value = "my value"

Or, if you wanted to use a With statement, you could use ..

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]

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]

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

 

[Smitty]

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]

Another option would be something like ...

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