Excel VBA:循环遍历单元格并将值复制到另一个工作簿。

I already spent hours on this problem, but I didn't succeed in finding a working solution.

我已经在这个问题上花了好几个小时，但是我没有找到一个有效的解决办法。

Here is my problem description:

以下是我的问题描述:

I want to loop through a certain range of cells in one workbook and copy values to another workbook. Depending on the current column in the first workbook, I copy the values into a different sheet in the second workbook. When I execute my code, I always get the runtime error 439: object does not support this method or property.

我想循环遍历一个工作簿中的特定范围的单元格，并将值复制到另一个工作簿。根据第一个工作簿中的当前列，我将值复制到第二个工作簿中的另一个表中。当我执行代码时，我总是会得到运行时错误439:object不支持这个方法或属性。

My code looks more or less like this:

我的代码大致是这样的:

Sub trial()

Dim Group As Range
Dim Mat As Range
Dim CurCell_1 As Range
Dim CurCell_2 As Range

Application.ScreenUpdating = False

Set CurCell_1 = Range("B3") 'starting point in wb 1

For Each Group in Workbooks("My_WB_1").Worksheets("My_Sheet").Range("B4:P4")
    Set CurCell_2 = Range("B4") 'starting point in wb 2
    For Each Mat in Workbooks("My_WB_1").Worksheets("My_Sheet").Range("A5:A29")
        Set CurCell_1 = Cells(Mat.Row, Group.Column) 'Set current cell in the loop
        If Not IsEmpty(CurCell_1)
            Workbooks("My_WB_2").Worksheets(CStr(Group.Value)).CurCell_2.Value = Workbooks("My_WB_1").Worksheets("My_Sheet").CurCell_1.Value 'Here it break with runtime error '438 object does not support this method or property
            CurCell_2 = CurCell_2.Offset(1,0) 'Move one cell down
        End If
    Next
Next

Application.ScreenUpdating = True

End Sub

I've done extensive research and I know how to copy values from one workbook to another if you're using explicit names for your objects (sheets & ranges), but I don't know why it does not work like I implemented it using variables. I also searched on stackoverlow and -obviously- Google, but I didn't find a similar problem which would answer my question.

我已经做了大量的研究，我知道如果要为对象(表和范围)使用显式名称，那么如何将值从一个工作簿复制到另一个工作簿，但是我不知道为什么它不能像我使用变量实现的那样工作。我还搜索了stackoverlow和——显然是谷歌，但是我没有发现类似的问题来回答我的问题。

So my question is: Could you tell me where the error in my code is or if there is another easier way to accomplish the same using a different way?

所以我的问题是:你能告诉我代码中的错误在哪里吗?或者有其他更简单的方法可以用不同的方式完成相同的任务吗?

This is my first question here, so I hope everything is fine with the format of my code, the question asked and the information provided. Otherwise let me know.

这是我的第一个问题，所以我希望我的代码格式、问题和提供的信息都没问题。否则请让我知道。

2 个解决方案

#1

5 Things...

5件事……

1) You don't need this line

你不需要这条线。

Set CurCell_1 = Range("B3") 'starting point in wb 1

设置CurCell_1 =范围(B3)“wb1的起点。

This line is pointless as you are setting it inside the loop

这一行没有任何意义，因为您正在循环中设置它

2) You are setting this in a loop every time

2)每次都要设置一个循环

Set CurCell_2 = Range("B4")

设置CurCell_2 =范围(B4)

Why would you be doing that? It will simply overwrite the values every time. Also which sheet is this range in??? (See Point 5)

你为什么要这么做?它只是每次都覆盖值。这个范围是什么???(见5点)

3)CurCell_2 is a Range and as JohnB pointed it out, it is not a method.

3)CurCell_2是一个范围，正如JohnB指出的，它不是一个方法。

Change

改变

Workbooks("My_WB_2").Worksheets(CStr(Group.Value)).CurCell_2.Value = Workbooks("My_WB_1").Worksheets("My_Sheet").CurCell_1.Value

手册(“My_WB_2”).Worksheets(装运箱(Group.Value)).CurCell_2。值=手册(My_WB_1).Worksheets .CurCell_1.Value(“My_Sheet”)

来

CurCell_2.Value = CurCell_1.Value

CurCell_2。值= CurCell_1.Value

4) You cannot assign range by just setting an "=" sign

4)您不能通过设置“=”符号来分配范围

CurCell_2 = CurCell_2.Offset(1,0)

CurCell_2 = CurCell_2.Offset(1,0)

Change it to

将其更改为

Set CurCell_2 = CurCell_2.Offset(1,0)

设置CurCell_2 = CurCell_2.Offset(1,0)

5) Always specify full declarations when working with two or more objects so that there is less confusion. Your code can also be written as (UNTESTED)

5)在处理两个或多个对象时，始终指定完整的声明，以便减少混淆。您的代码也可以写成(未经测试的)

Option Explicit

Sub trial()
    Dim wb1 As Workbook, wb2 As Workbook
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim Group As Range, Mat As Range
    Dim CurCell_1 As Range, CurCell_2 As Range

    Application.ScreenUpdating = False

    '~~> Change as applicable
    Set wb1 = Workbooks("My_WB_1")
    Set wb2 = Workbooks("My_WB_2")

    Set ws1 = wb1.Sheets("My_Sheet")
    Set ws2 = wb2.Sheets("Sheet2") '<~~ Change as required

    For Each Group In ws1.Range("B4:P4")
        '~~> Why this?
        Set CurCell_2 = ws2.Range("B4")
        For Each Mat In ws1.Range("A5:A29")
            Set CurCell_1 = ws1.Cells(Mat.Row, Group.Column)
            If Not IsEmpty(CurCell_1) Then
                CurCell_2.Value = CurCell_1.Value
                Set CurCell_2 = CurCell_2.Offset(1)
            End If
        Next
    Next

    Application.ScreenUpdating = True
End Sub

#2

Workbooks("My_WB_2").Worksheets(CStr(Group.Value)).CurCell_2.Value

This will not work, since CurCell_2 is not a method of Worksheet, but a variable. Replace by

这将不起作用，因为CurCell_2不是工作表的方法，而是一个变量。取代

Workbooks("My_WB_2").Worksheets(CStr(Group.Value)).Range("B4").Value

#1