Excel VBA在50张图纸上运行宏并应用简单除法

Question

我正在尝试在50个选项卡上运行以下宏。"自动筛选"宏确实适用于各个选项卡，但我希望每个数字除以一个总数字的宏不起作用。基本上，从第5行开始的任何内容都不会应用于所有选项卡，它只应用于一个选项卡。有人能帮忙吗？

Sub Macro2()
'
' Macro2 Macro
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Range("A1:AC91").AutoFilter Field:=7, Criteria1:=Array("11" _
, "21", "22", "23", "31-33", "42", "44-45", "48-49", "51", "52", "53", "54", "55", "56", "61" _
, "62", "71", "72", "81"), Operator:=xlFilterValues
Range("AD1").Select
ActiveCell.FormulaR1C1 = "In %"
Range("AD1").Select
Selection.Font.Bold = True
Range("AD2").Select
Application.CutCopyMode = False
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=RC[-2]/R2C28"
Range("AD2").Select
Selection.Copy
Range("AD2:AD91").Select
Application.CutCopyMode = False
Selection.FillDown
Selection.Style = "Percent"
Selection.NumberFormat = "0.0%"
Selection.Font.Bold = True
Range("AD2").Select
Next ws
End Sub

Answer 1

您需要将每一行以Range和ws.开头作为前缀，否则您的目标是ActiveWorksheet，而不是循环中的那个。

Sub Macro2()
'
' Macro2 Macro
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Range("A1:AC91").AutoFilter Field:=7, Criteria1:=Array("11" _
, "21", "22", "23", "31-33", "42", "44-45", "48-49", "51", "52", "53", "54", "55", "56", "61" _
, "62", "71", "72", "81"), Operator:=xlFilterValues
ws.Range("AD1").Select
ActiveCell.FormulaR1C1 = "In %"
ws.Range("AD1").Select
Selection.Font.Bold = True
ws.Range("AD2").Select
Application.CutCopyMode = False
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=RC[-2]/R2C28"
ws.Range("AD2").Select
Selection.Copy
ws.Range("AD2:AD91").Select
Application.CutCopyMode = False
Selection.FillDown
Selection.Style = "Percent"
Selection.NumberFormat = "0.0%"
Selection.Font.Bold = True
ws.Range("AD2").Select
Next ws
End Sub