excel 循环VBA除MasterSheet外的所有工作表

9udxz4iz 于 2023-01-21 发布在其他

关注(0)|答案(2)|浏览(224)

我的密码是

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

所以我在代码之前插入了这段代码

Dim ws As Worksheet
   For Each ws In Sheets

并插入更多prepend每个范围...

ws.Range("A7").Select

但它对我不起作用。那么有没有其他的方法来做到这一点？循环VBA的所有工作表在该工作簿中，除了"主表"
关于我的数据，它看起来像这样：
enter image description here
所以我想改变A7：A结束向下是值B4。-然后过滤，过滤列T，V每个值= 0，并删除它

替换一些名称，这是运行marco 1工作表后的最终结果：enter image description here

enter image description here

excel

来源：https://stackoverflow.com/questions/75168542/loop-vba-all-sheet-except-mastersheet

2条答案

按热度按时间

lrpiutwd1#

这是我通常遍历所有工作表的方式，但有一个例外：

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

在ws上运行的代码可能类似于以下内容：

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

赞(0）回复(0）举报 2023-01-21

3bygqnnd2#

当然，这不是最干净和最好的解决方案（参见注解“避免选择....”），但请保持代码不变，为调用它创建一个sub，如下所示：

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

赞(0）回复(0）举报 2023-01-21

我来回答

excel 循环VBA除MasterSheet外的所有工作表

2条答案

相关问题

热门标签

最新问答