Loop VBA All Sheet Except MasterSheet-CodePudding

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"

CodePudding user response：

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

CodePudding user response：

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