使用For Each是正确的，但您需要测试工作表是否可见（未隐藏）并被选中。

Sub demo()

    Dim ws As Worksheet, wb As Workbook
    
    Set wb = ThisWorkbook
    
    For Each ws In wb.Sheets
        If ws.Visible = xlSheetVisible And IsWSSelected(ws, wb) Then
            CopyRetentionData
        End If
    Next

End Sub

我得到了一个函数来检查是否从MrExcel中选择了一个工作表

Function IsWSSelected(wsToTest As Worksheet, Optional wb As Workbook) As Boolean
'Credit: Greg Truby https://www.mrexcel.com/board/threads/check-if-worksheet-is-selected.193955/
    Dim ws As Worksheet
    If wb Is Nothing Then Set wb = ThisWorkbook
    For Each ws In wb.Windows(1).SelectedSheets
        If ws Is wsToTest Then IsWSSelected = True: Exit Function
    Next ws
End Function

您需要更改在CopyRetentionData例程中放置输出的位置，即将outputRow = 2替换为outputRow = lastRow(ws) + 1我使用以下代码查找工作表中的最后一行

Public Function lastRow(rg As Variant) As Long
'Find the last row used in a worksheet - regardless of where the data starts and ends
'and regardless of whether the data is contiguous or jagged
'Note use of Variant for the input range parameter
'This allows both worksheets and ranges to passed into the function
    On Error Resume Next
    Dim lr As Long
    lr = rg.Cells.Find(What:="*" _
                    , LookAt:=xlPart _
                    , LookIn:=xlFormulas _
                    , SearchOrder:=xlByRows _
                    , SearchDirection:=xlPrevious).Row
    If Err.Number <> 0 Then
        lr = 1  'cater for a completely empty sheet
    End If
    On Error GoTo 0
    lastRow = lr
End Function