Loop VBA All Sheet Except MasterSheet

Question

My code is

Sub Macro5()
'
' Macro5 Macro


'
    Range("A7").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Columns.AutoFit
    Range("A8").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=R4C2"
    Range("A8").Select
    Selection.Copy
    Range(Selection, Selection.End(xlDown)).Select
    ActiveSheet.Paste
    Range("A7").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.AutoFilter
    Selection.Columns.AutoFit
    ActiveWindow.SmallScroll ToRight:=15
    ActiveSheet.Range("$A$7:$AC$38").AutoFilter Field:=20, Criteria1:="0"
    ActiveSheet.Range("$A$7:$AC$38").AutoFilter Field:=22, Criteria1:="0"
    ActiveWindow.SmallScroll ToRight:=-45
    Range("A13").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.EntireRow.Delete
    Cells.Select
    Range("A22").Activate
    Selection.AutoFilter
    Range("A11").Select
    Columns("E:E").Select
    Application.CutCopyMode = False
    Selection.Replace What:="Discovery Ads", Replacement:="Qua" & ChrW(777) & "ng Ca" & ChrW(769) & "o Kha" _
         & ChrW(769) & "m Pha" & ChrW(769) _
        , LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat _
        :=False, ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
    Columns("E:E").Select
    Selection.Replace What:="Product Search Ad", Replacement:= _
        "Qua" & ChrW(777) & "ng Ca" & ChrW(769) & "o Ti" _
        & ChrW(768) & "m Kiê" & ChrW(769) & "m Sa" & ChrW(777) & "n Phâ" & ChrW( _
        777) & "m", LookAt:=xlPart, SearchOrder:=xlByRows, _
        MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False, _
        FormulaVersion:=xlReplaceFormula2
    Columns("E:E").Select
    Selection.Replace What:="Shop Search Ad", Replacement:= _
        "Qua" & ChrW(777) & "ng Ca" & ChrW(769) & "o Ti" _
        & ChrW(768) & "m Kiê" & ChrW(769) & "m Shop", LookAt:=xlPart, SearchOrder:=xlByRows, _
        MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False, _
        FormulaVersion:=xlReplaceFormula2
         Selection.AutoFilter
End Sub

So i have insert this code before code

 Dim ws As Worksheet
   For Each ws In Sheets

And insert more prepend every Range...Select like

ws.Range("A7").Select

But it not work for me. So is there any other way to do it?. Loop VBA all sheet in that workbook except "MasterSheet"

About my data it look like this :

enter image description here

So i wanna change A7:A end down is value B4. -Then filter, Filter column T,V every value = 0 and delete it

Replace some name And this is final after run marco 1 sheet : enter image description here

enter image description here

Welcome to SO. "macro VBA" always needs cleaning up - I recommend reading [How to avoid using select](https://stackoverflow.com/a/23913882/16578424) and [How to avoid copy/paste](https://stackoverflow.com/a/64611707/16578424) - if you apply those adivses, the code will look cleaner and you might find the issues. — Ike, Jan 19 '23 at 07:09
Please [edit] your question, add a screenshot of the data and describe in words what you expect that the code should do. This is a recorded macro and therefore needs to be optimized to work on multiple worksheets. — Pᴇʜ, Jan 19 '23 at 07:36
Thank for your recommend @Ike. I will try that and apply my code to clean up — Hoàng Lê, Jan 21 '23 at 04:11
@ peh _add a **screenshot** of the data_ Nooooooo! If you want to retype some data from a screenshot, you go right ahead. — chris neilsen, Jan 21 '23 at 06:38

CLR · Answer 1 · 2023-01-19T09:15:51.713

This is how I would normally loop through all sheets with an exception:

Sub loop_through_sheets()
    Dim ws As Worksheet
    For Each ws In Sheets
      If ws.Name <> "Mastersheet" Then
          '... run the code on ws
      Else
          '.. do nothing
      End If
    Next
End Sub

Your code to run on ws might look something like this:

            With ws
                'auto fit the columns
                .Range("A:AC").Columns.AutoFit

                'find the last populated cell in column A
                Dim lastrow as Long
                lastrow=ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

                'insert the formula
                .Range("A8:A" & lastrow).FormulaR1C1 = "=R4C2"
    
                'etc. etc.
            End with

score -2 · Answer 2 · answered Jan 19 '23 at 07:45

-2

of course this is not the cleanest and best solution (see comment "avoid select...."), but leave the code as is it, make a sub for calling it like:

sub callmymacro
Dim ws As Worksheet
   For Each ws In Sheets
   if ws.Name = "Mastersheet" GoTo notthisone
   Sheets(ws).Select
   call Macro5
   notthisone:
   Next
end sub

answered Jan 19 '23 at 07:45

Max

744
1
7
19

1

Very bad design using `GoTo`, especially if you can easily avoid it with a `If … Then … End If` statement. Avoid `GoTo` unless it is for error handling `On Error GoTo` it's a bad practice. – Pᴇʜ Jan 19 '23 at 09:36
Why is goTo bad? – Max Jan 24 '23 at 09:24
1

Because it results in messy code ([spaghetti code](https://en.wikipedia.org/wiki/Spaghetti_code)) that is hard to read and follow. If code is hard to read/understand and maintain it is considered bad code because it leads to more errors. Good code is clean, easy to read and understand. This leads to less errors. Since we have structured programming (functions, conditionals, loops) you can avoid the messy `GoTo` and replace it with a nice clean structure. Why is it still there then? In some rare cases you will need it: To jump out of nested loops for example. – Pᴇʜ Jan 24 '23 at 09:40

Loop VBA All Sheet Except MasterSheet

2 Answers2