VBA sort in a range of sheets, not all sheets

[Andrewryan3]

I have found this VBA which will sort all the sheets I have in a workbook, but I only want to sort a few....can someone tell me should I name all sheets in the () or how can I do this. There are sheets before and after the 42 that I need sorted....or is there a shorter easier way??

<code class="vb keyword">Sub</code> <code class="vb plain">SortAllSheets()</code>
<code class="vb spaces"> </code><code class="vb comments">'Updateby Extendoffice 20161216</code>
<code class="vb spaces"> </code><code class="vb keyword">Dim</code> <code class="vb plain">WS </code><code class="vb keyword">As</code> <code class="vb plain">Worksheet</code>
<code class="vb spaces"> </code><code class="vb plain">ActiveSheet.Range(</code><code class="vb string">"a1:f1"</code><code class="vb plain">).</code><code class="vb keyword">Select</code>
<code class="vb spaces"> </code><code class="vb plain">Selection.Copy</code>
<code class="vb spaces"> </code><code class="vb keyword">On</code> <code class="vb keyword">Error</code> <code class="vb keyword">Resume</code> <code class="vb keyword">Next</code>
<code class="vb spaces"> </code><code class="vb plain">Application.ScreenUpdating = </code><code class="vb keyword">False</code>
<code class="vb spaces"> </code><code class="vb keyword">For</code> <code class="vb keyword">Each</code> <code class="vb plain">WS </code><code class="vb keyword">In</code> <code class="vb plain">Worksheets</code>
<code class="vb spaces"> </code><code class="vb plain">WS.Columns(</code><code class="vb string">"A:F"</code><code class="vb plain">).Sort Key1:=WS.Columns(</code><code class="vb string">"E"</code><code class="vb plain">), Order1:=xlDescending</code>
<code class="vb spaces"> </code><code class="vb keyword">Next</code> <code class="vb plain">WS</code>
<code class="vb spaces"> </code><code class="vb plain">ActiveSheet.Range(</code><code class="vb string">"A1"</code><code class="vb plain">).PasteSpecial Paste:=xlPasteAll</code>
<code class="vb spaces"> </code><code class="vb plain">Application.ScreenUpdating = </code><code class="vb keyword">True</code>
<code class="vb keyword">End</code> <code class="vb keyword">Sub</code>

 

[MisterProzilla]

It's maybe a bit messy, but one way you could do it is to make it dependent on an if statement, by putting a marker somewhere in those sheets that you want to sort. So designate a certain hidden cell in each sheet - let's say cell Z1 for example, and make it so your sort only runs if it finds a 'Y' in that cell, otherwise it just skips over to the next worksheet:

<code class="vb keyword" style="margin: 0px; padding: 0px; line-height: 13px; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">Sub</code>[COLOR=#333333] [/COLOR]<code class="vb plain" style="margin: 0px; padding: 0px; line-height: 13px; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">SortAllSheets()</code>
<code class="vb spaces" style="margin: 0px; padding: 0px; line-height: 13px; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);"></code><code class="vb comments" style="margin: 0px; padding: 0px; line-height: 13px; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">'Updateby Extendoffice 20161216</code>
<code class="vb spaces" style="margin: 0px; padding: 0px; line-height: 13px; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);"></code><code class="vb keyword" style="margin: 0px; padding: 0px; line-height: 13px; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">Dim</code>[COLOR=#333333] [/COLOR]<code class="vb plain" style="margin: 0px; padding: 0px; line-height: 13px; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">WS </code><code class="vb keyword" style="margin: 0px; padding: 0px; line-height: 13px; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">As</code>[COLOR=#333333] [/COLOR]<code class="vb plain" style="margin: 0px; padding: 0px; line-height: 13px; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">Worksheet</code>
<code class="vb spaces" style="margin: 0px; padding: 0px; line-height: 13px; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);"></code><code class="vb plain" style="margin: 0px; padding: 0px; line-height: 13px; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">ActiveSheet.Range(</code><code class="vb string" style="margin: 0px; padding: 0px; line-height: 13px; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">"a1:f1"</code><code class="vb plain" style="margin: 0px; padding: 0px; line-height: 13px; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">).</code><code class="vb keyword" style="margin: 0px; padding: 0px; line-height: 13px; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">Select</code>
<code class="vb spaces" style="margin: 0px; padding: 0px; line-height: 13px; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);"></code><code class="vb plain" style="margin: 0px; padding: 0px; line-height: 13px; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">Selection.Copy</code>
<code class="vb spaces" style="margin: 0px; padding: 0px; line-height: 13px; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);"></code><code class="vb keyword" style="margin: 0px; padding: 0px; line-height: 13px; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">On</code>[COLOR=#333333] [/COLOR]<code class="vb keyword" style="margin: 0px; padding: 0px; line-height: 13px; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">Error</code>[COLOR=#333333] [/COLOR]<code class="vb keyword" style="margin: 0px; padding: 0px; line-height: 13px; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">Resume</code>[COLOR=#333333] [/COLOR]<code class="vb keyword" style="margin: 0px; padding: 0px; line-height: 13px; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">Next</code>
<code class="vb spaces" style="margin: 0px; padding: 0px; line-height: 13px; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);"></code><code class="vb plain" style="margin: 0px; padding: 0px; line-height: 13px; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">Application.ScreenUpdating = </code><code class="vb keyword" style="margin: 0px; padding: 0px; line-height: 13px; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">False
</code><code class="vb keyword" style="margin: 0px; padding: 0px; line-height: 13px; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">For</code>[COLOR=#333333] [/COLOR]<code class="vb keyword" style="margin: 0px; padding: 0px; line-height: 13px; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">Each</code>[COLOR=#333333] [/COLOR]<code class="vb plain" style="margin: 0px; padding: 0px; line-height: 13px; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">WS </code><code class="vb keyword" style="margin: 0px; padding: 0px; line-height: 13px; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">In</code>[COLOR=#333333] [/COLOR]<code class="vb plain" style="margin: 0px; padding: 0px; line-height: 13px; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">Worksheets
</code> [B]  If ws.range("Z1").value = "Y" then
<code class="vb spaces" style="margin: 0px; padding: 0px; line-height: 13px; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);"></code><code class="vb plain" style="margin: 0px; padding: 0px; line-height: 13px; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">     WS.Columns(</code><code class="vb string" style="margin: 0px; padding: 0px; line-height: 13px; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">"A:F"</code><code class="vb plain" style="margin: 0px; padding: 0px; line-height: 13px; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">).Sort Key1:=WS.Columns(</code><code class="vb string" style="margin: 0px; padding: 0px; line-height: 13px; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">"E"</code><code class="vb plain" style="margin: 0px; padding: 0px; line-height: 13px; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">), Order1:=xlDescending
</code>   End If[/B]
<code class="vb spaces" style="margin: 0px; padding: 0px; line-height: 13px; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);"></code><code class="vb keyword" style="margin: 0px; padding: 0px; line-height: 13px; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">Next</code>[COLOR=#333333] [/COLOR]<code class="vb plain" style="margin: 0px; padding: 0px; line-height: 13px; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">WS
</code><code class="vb plain" style="margin: 0px; padding: 0px; line-height: 13px; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">ActiveSheet.Range(</code><code class="vb string" style="margin: 0px; padding: 0px; line-height: 13px; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">"A1"</code><code class="vb plain" style="margin: 0px; padding: 0px; line-height: 13px; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">).PasteSpecial Paste:=xlPasteAll</code>
<code class="vb spaces" style="margin: 0px; padding: 0px; line-height: 13px; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);"></code><code class="vb plain" style="margin: 0px; padding: 0px; line-height: 13px; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">Application.ScreenUpdating = </code><code class="vb keyword" style="margin: 0px; padding: 0px; line-height: 13px; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">True
</code><code class="vb keyword" style="margin: 0px; padding: 0px; line-height: 13px; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">End</code>[COLOR=#333333] [/COLOR]<code class="vb keyword" style="margin: 0px; padding: 0px; line-height: 13px; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">Sub</code>

Is that the kind of thing you're after?