如何遍历工作簿中的所有工作表（排除名为Summary的工作表）

Got it, let's adjust your code to loop through every sheet in your workbook except the "Summary" one. Here's how to do it step by step:

We'll wrap your existing row-loop inside a worksheet loop, explicitly reference each sheet to avoid active sheet bugs, and add a check to skip the "Summary" sheet entirely.

Here's the updated code:

Dim ws As Worksheet
Dim i As Long, k As Long, LR As Long

' Initialize k to your starting row on Sheet1 (adjust this value as needed)
k = 2 ' Assuming you start pasting at row 2 on Sheet1

' Loop through each worksheet in the current workbook
For Each ws In ThisWorkbook.Worksheets
    ' Skip the "Summary" sheet entirely
    If ws.Name <> "Summary" Then
        ' Get the last row with data in column J of the current sheet
        LR = ws.Cells(ws.Rows.Count, "J").End(xlUp).Row
        
        ' Your original row loop, now targeting the current worksheet (ws)
        For i = 2 To LR
            If Abs(ws.Range("J" & i).Value) > 0 Then
                ws.Range("A" & i & ":J" & i).Copy
                Sheets("Sheet1").Range("A" & k).PasteSpecial Paste:=xlPasteValues, _
                    Operation:=xlNone, SkipBlanks:=False, Transpose:=False
                Application.CutCopyMode = False
                k = k + 1
            End If
        Next i
    End If
Next ws

Key improvements made: #

• Added a Worksheet variable (ws) to iterate through each sheet in the workbook, so we don't rely on the unpredictable active sheet.
• Added a condition If ws.Name <> "Summary" Then to skip processing that sheet.
• Prefixed all range references with ws. to ensure we're working on the correct sheet during each iteration.
• Calculated LR dynamically for each sheet using ws.Cells(ws.Rows.Count, "J").End(xlUp).Row, so it adapts to how much data each sheet has.
• Kept k initialized outside the worksheet loop, so it continues incrementing across all sheets instead of resetting for each one.

This code will process every sheet except "Summary", copying rows where column J has a non-zero absolute value into Sheet1 in a continuous sequence.

内容的提问来源于stack exchange，提问作者June Smith