Excel - VBA Macro - Stopping code at a specific sheet

Question

I have a number of sheets with the same template where I want to sort the date field. I've been doing it manually but am trying VBA to do it for me. I have the code below which works but it applies to more sheets than I'd like. I am trying to figure out how to stop the macro to stop at a specific sheet and end it there.

Goal: have macro run from sheet 1-10, stop @ sheet 10 or if worksheet = Sheet 11 then stop. I am using sheet 1-10, 11 as simple references. I'd insert the specific sheet name.

I found some answers online with -

If ws.Name <> "" Then
end with

but am not sure where to input it within my macro below.

Sub Macro1()
'
' sortbydate2 Macro
'

'
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets

    With ws
        With .Sort
            .SortFields.Clear
            .SortFields.Add Key:=Range("A2:a49"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            .SetRange ws.Cells
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    End With
Next ws
End Sub

Thank you, P1

Thank you for all your answers. will give it a shot shortly. — p1boxnow
– p1boxnow, Commented Aug 25, 2020 at 12:31

VBasic2008 · Accepted Answer · 2020-08-24 23:11:03Z

Manipulate All Worksheets Except...

You can implement an 'exceptions array' where you would preferably place the names, or the indexes (not recommended) of the unwanted worksheets.
Then you can use IsError with Application.Match to check if the name of the current worksheet has been found in the 'exceptions array'.

The Code

Option Explicit

Sub Macro1()
'
' sortbydate2 Macro
'

'
    Dim Exceptions As Variant
    Exceptions = Array("Sheet11", "Sheet12") ' add more or less.
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        If IsError(Application.Match(ws.Name, Exceptions, 0)) Then
            With ws
                With .Sort
                    .SortFields.Clear
                    .SortFields.Add Key:=Range("A2:a49"), _
                                    SortOn:=xlSortOnValues, _
                                    Order:=xlAscending, _
                                    DataOption:=xlSortNormal
                    .SetRange ws.Cells
                    .Header = xlYes
                    .MatchCase = False
                    .Orientation = xlTopToBottom
                    .SortMethod = xlPinYin
                    .Apply
                End With
            End With
        End If
    Next ws

End Sub

user1628449 · Accepted Answer · 2020-08-24 20:31:23Z

0

You could loop through all worksheets within the workbook and apply the filter to all except - something like:

For Each ws In ActiveWorkbook.Worksheets
      if ws.Name <> "IDontWantFilters" Then
          with ws
            ....
          end with
     end if
next ws

answered Aug 24, 2020 at 20:31

user1628449

3851 gold badge5 silver badges16 bronze badges

Comments

Lewis Morris · Accepted Answer · 2020-08-24 20:33:43Z

0

I think this should work. I assume once it gets to sheet11 you just want it to stop completly

Sub Macro1()


Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets

    if ws.name = "Sheet11" then
        exit sub
    end if

    With ws
        
        With .Sort
            .SortFields.Clear
            .SortFields.Add Key:=Range("A2:a49"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            .SetRange ws.Cells
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    End With
Next ws
End Sub

answered Aug 24, 2020 at 20:33

Lewis Morris

2,2144 gold badges35 silver badges46 bronze badges

Comments

GMalc · Accepted Answer · 2020-08-25 00:20:08Z

0

If you only want to sort the first 10 worksheets, you could do a basic loop to accomplish your task...

Dim ws As Worksheet
For i = 1 To 10
    Set ws = Sheets(i)
        
    With ws.Sort
        .SortFields.Clear
        .SortFields.Add Key:=Range("A2:A49"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .SetRange Sheets(i).Cells
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
Next i

answered Aug 25, 2020 at 0:20

GMalc

2,6381 gold badge11 silver badges16 bronze badges

Collectives™ on Stack Overflow

Excel - VBA Macro - Stopping code at a specific sheet

4 Answers 4

Manipulate All Worksheets Except...

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Manipulate All Worksheets Except...

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related