excel - VBA 运行宏然后遍历其他工作表

Question

我正在尝试（但失败）让一些代码在每个工作表上运行，除了一个特定的工作表。我希望代码只剪切单元格 n2:s2 中的数据并将其粘贴到 t1:y1 中，然后对在 n3:s3、n4:s4、n5:s5 列中具有数据的任何其他行重复。

一旦没有数据（我相信第 6 行），它应该移动到下一张表（“报告”表除外）。我调试时面临的问题是它按预期移动数据，然后在同一张纸上重新开始，因此用空单元格覆盖数据。

Sub MovethroughWB()

    Dim ws As Worksheet

    For Each ws In ThisWorkbook.Worksheets 'This statement starts the loop

        If ws.Name <> "Report" Then 'Perform the Excel action you wish (turn cell yellow below)

            Range("N2:S2").Select
            Selection.Cut Destination:=Range("T1:Y1")
            Range("T1:Y1").Select
            Range("N3:S3").Select
            Selection.Cut Destination:=Range("Z1:AE1")

        End If

    Next ws

End Sub

我确定它是基本的，但找不到什么！

标签： excelvbaloops

Answer 1

尝试：

Sub MovethroughWB()

    Dim ws As Worksheet
    Dim i  As Long, Lastrow As Long, Lastcolumn As Long

    For Each ws In ThisWorkbook.Worksheets 'This statement starts the loop
        If ws.Name <> "Report" Then 'Perform the Excel action you wish (turn cell yellow below)
            With ws
                Lastrow = .Cells(.Rows.Count, "N").End(xlUp).Row
                For i = 2 To Lastrow
                    If .Range("N" & i).Value <> "" And .Range("O" & i).Value <> "" And .Range("P" & i).Value <> "" _
                        And .Range("Q" & i).Value <> "" And .Range("R" & i).Value <> "" And .Range("S" & i).Value <> "" Then
                        If .Range("T1").Value = "" Then
                            .Range("N" & i & ":S" & i).Cut .Range("T1:Y1")
                        Else
                            Lastcolumn = .Cells(1, .Columns.Count).End(xlToLeft).Column + 1
                            .Range("N" & i & ":S" & i).Cut .Range(.Cells(1, Lastcolumn), .Cells(1, Lastcolumn + 5))
                        End If
                    End If
                Next i

                .Rows("2:" & Lastrow).EntireRow.Delete

            End With

        End If

    Next ws

End Sub