Excel VBA循环遍历多个工作表

VBA beginner here, I've got a little problem with program I'm working on.

VBA初学者在这里，我对我正在研究的程序有一点问题。

I need to copy data from last cell in column B from first worksheet and paste it into column A in another worksheet xws, and repeate this operation for five other worksheets with data.

我需要从第一个工作表中复制B列中最后一个单元格中的数据，然后将其粘贴到另一个工作表xws中的A列中，并对包含数据的其他五个工作表重复此操作。

Here's the code, it doesn't work the way it should:

这是代码，它不应该按照它应该的方式工作：

Sub exercise()

    Dim ws As Worksheet
    Dim rng As Range
    'Finding last row in column B
    Set rng = Range("B" & Rows.Count).End(xlUp)

    For Each ws In ActiveWorkbook.Worksheets
        'Don't copy data from xws worksheet
        If ws.Name <> "xws" Then
            'Storing first copied data in A1
            If IsEmpty(Sheets("xws").[A1]) Then
                rng.Copy Sheets("xws").Range("A" & Rows.Count).End(xlUp)
            'Storing next copied data below previously filled cell
            Else
                rng.Copy Sheets("xws").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
            End If
        End If
    Next ws
End Sub

There is a problem with ws. referring, but whenever I put it before rng in if statements or before range (set rng = ...) I get errors.

ws存在问题。引用，但无论何时我把它放在if语句之前或范围之前（设置rng = ...）我都会得到错误。

Thanks in advance for any pointers.

提前感谢任何指针。

1 个解决方案

#1

You should be declaring rng for each ws inside the loop, like:

你应该为循环中的每个ws声明rng，例如：

Sub exercise()
    Dim ws As Worksheet
    Dim rng As Range

    For Each ws In ActiveWorkbook.Worksheets
        'Finding last row in column B
        Set rng = ws.Range("B" & ws.Rows.Count).End(xlUp) '<~~ Moved inside the loop
        'Don't copy data from xws worksheet
        If ws.Name <> "xws" Then
            'Storing first copied data in A1
            If IsEmpty(Sheets("xws").[A1]) Then
                rng.Copy Sheets("xws").Range("A" & Rows.Count).End(xlUp)
            'Storing next copied data below previously filled cell
            Else
                rng.Copy Sheets("xws").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
            End If
        End If
    Next ws
End Sub

As your code is now, rng will be pointing to the ActiveSheet at the time you run the macro, and your code will then copy the same cell on each iteration of the code.

正如您的代码现在一样，rng将在您运行宏时指向ActiveSheet，然后您的代码将在代码的每次迭代中复制相同的单元格。

#1