Do until sheet loop with exclusion

Question

In the below , the macro will loop till the sheet "XYZ". There are 10 sheets before the sheet "XYZ".

In those 10 sheets i dont want the macro to run on sheet "XBA" and sheet "XBZ".

Please help. I am unable to find exact vba code for this kind of a loop.

Sub Fexp1()
Sheets("AAA").Activate
i = 4
Do Until ActiveSheet.Name = "XYZ"
If ActiveSheet.AutoFilterMode = True Then ActiveSheet.AutoFilterMode = False
    
    Dim sh As Worksheet
    Dim rng As Range

    Set sh = ActiveSheet

    sh.AutoFilterMode = False
    sh.Range("$A7:$M7").AutoFilter Field:=7, Criteria1:="x1"

    Rowz = sh.AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Cells.Count - 1
    If Rowz <> 0 Then

    Dim rTable As Range
    Set rTable = ActiveSheet.AutoFilter.Range
    Set rTable = rTable.Resize(rTable.Rows.Count - 1)
    Set rTable = rTable.Offset(1)
    rTable.Copy
    Sheets("EXP1").Select
    Range("A1").Select
    ActiveCell.End(xlDown).Offset(1, 0).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
End If
  Sheets(i + 1).Activate
  i = i + 1
  
Loop
Sheets("EXP1").Select
  Columns("F:F").Select
    Range("F2").Activate
    Selection.NumberFormat = "dd/mm/yyyy"
End Sub

score 0 · Answer 1 · answered Sep 01 '20 at 07:12

0

In those 10 sheets i dont want the macro to run on sheet "XBA" and sheet "XBZ".

I would recommned using a For Loop with Select Case. It will be easier to handle

Here is an example to achieve what you want.

Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets
    Select Case ws.Name
    Case "XBA", "XBZ"
        '~~> Do nothing
    Case Else
        '~~> Do what you want here
    End Select
Next ws

Also avoid the use of .Select. You may want to read up on How to avoid using Select in Excel VBA

Here is an example. The below lines

Sheets("EXP1").Select
Columns("F:F").Select
Range("F2").Activate
Selection.NumberFormat = "dd/mm/yyyy"

can also be written as

Sheets("EXP1").Columns(6).NumberFormat = "dd/mm/yyyy"

answered Sep 01 '20 at 07:12

Siddharth Rout

147,039
17
206
250

I've just done exactly that, hence I've deleted my comment. Your code doesn't activate F2 however. – JMP Sep 01 '20 at 07:30
@JMP: Thats the point. No need to activate F2. I think the code came there because user was recording a macro. – Siddharth Rout Sep 01 '20 at 07:31
@SiddharthRout thanks for the advise. But this method is not working. In this method the macro is running only in the 1st sheet and once it finishes it is not switching to second sheet , instead it is running the macro in Sheet EXP1. Ideally it should run in 2 sheet (ABA) after 1 sheet (AAA). – vignesh95 Sep 01 '20 at 09:09
It is not possible that this method will not work. It **WILL** loop though all worksheets and skip "XBA" and "XBZ". Can you post the code that you now have. I feel that you are doing something wrong. @vignesh95 – Siddharth Rout Sep 01 '20 at 09:23
Check it out in this "https://www.appdevtools.com/pastebin/hSYLXz" – vignesh95 Sep 01 '20 at 10:53
`If ActiveSheet.AutoFilterMode = True Then ActiveSheet.AutoFilterMode = False` Do not use activesheet. What is th epoing of the loop then. change it to `ws.AutoFilterMode` and so on – Siddharth Rout Sep 01 '20 at 11:00

Do until sheet loop with exclusion

1 Answers1