excel - 如何仅通过可见选项卡运行代码？

Question

我有一个相当琐碎的问题。我有这段代码[如您所见，这是特定于工作表的，我需要的是它通过所有可见的 [非隐藏] 工作表，您能建议吗？]：

Sub TRANS_to_EDD()

Dim WB As Workbook
Dim LTD As Worksheet
Dim INF As Long, RSP As Long

Set WB = Workbooks("XX.xlsm")
Set LTD = WB.Sheets("LimitedCo")
Set EDD = WB.Sheets("EDD")

INF = LTD.Range("A" & Rows.Count).End(xlUp).Row

For i = 2 To INF
If LTD.Range("I" & i).Value = "Reject" Then
    LTD.Range("I" & i).Offset(0, -6).Copy
    EDD.Range("C" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
End If
Next i

For i = 2 To INF
If LTD.Range("I" & i).Value = "Reject" Then
    LTD.Range("I" & i).Offset(0, -5).Copy
    EDD.Range("E" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
End If
Next i

Application.CutCopyMode = False
End Sub

谢谢

PS。EDD 始终保持不变，因为它是最终目的地。

标签： excelvba

Answer 1

只需遍历工作表，检查它们是否可见：

Dim ws As Worksheet
For Each ws In Workbooks("XX.xlsm").Worksheets
    If ws.Visible = xlSheetVisible Then
        ' Do things
    End If
Next ws