合并两个工作表 [英] Consolidating two Worksheets

问题描述

我想根据注册号"合并两个工作表.在第三个工作表中.

I want to consolidate two worksheets on the basis of a "Register No." in a third worksheet.

Workbook:
Tabelle1: Consolidated Worksheet //
Tabelle2: Input Data1 //
Tabelle3: Input Data2

注释:

Notes:

• 首先，注册号"只能在Tabelle2&列A中的Tabelle3.

• At first the "Register No." can only be found in Tabelle2 & Tabelle3 in column A.

因为Tabelle1的列顺序也不同于Tabelle2&Tabelle3我正在使用vLookup将数据粘贴到Tabelle1的右列.

Because Tabelle1 has also a different column sequence than Tabelle2 & Tabelle3 I am using vLookup to paste the data to the right columns in Tabelle1.

想法:

Idea:

1.步骤通过vLookup将Tabelle2数据(包括寄存器号")粘贴到Tabelle1的右列中.注意:这表示注册号".到Tabelle 1列A.

1. Step Pasting Tabelle2 Data, including "Register No.", to the right columns in Tabelle1 via vLookup. Note: This means "Register No." to Tabelle 1 column A.

2.步骤通过vLookup将Tabelle3数据粘贴到Tabelle1的右侧行和列中.由于Tabelle3包含更多的注册号".比Tabelle2，我希望我的代码检查注册号".在Tabelle1列A中复制并从Tabelle3复制相应的数据.

2. Step Pasting Tabelle3 Data to right rows and columns in Tabelle1 via vLookup. As Tabelle3 contains more "Register No." than Tabelle2, I want my code to check the "Register No." in Tabelle1 column A and copy the corresponding data from Tabelle3.

错误:

2.步骤不起作用.

运行时错误'1004'

Runtime-Error '1004'

例如:

For i = 2 To lastrow2

    Tabelle1.Cells(7 + i, 2) = Application.WorksheetFunction.VLookup(Tabelle1.Cells(i, 1), myrange2, 2, False)

Next i

有人知道我的代码有什么问题吗?非常感谢:)

Does anyone know what is wrong with my code? Thanks a lot :)

我的代码:

Sub ConsolidateData()


Dim lastrow As Long
lastrow = Tabelle2.Range("A" & Rows.Count).End(xlUp).Row
Set myrange = Tabelle2.UsedRange


For i = 2 To lastrow
    Tabelle1.Cells(7 + i, 1) = Application.WorksheetFunction.VLookup(Tabelle2.Cells(i, 1), myrange, 1, False)
Next i

For i = 2 To lastrow
    Tabelle1.Cells(7 + i, 3) = Application.WorksheetFunction.VLookup(Tabelle2.Cells(i, 1), myrange, 2, False)
Next i



For i = 2 To lastrow
Tabelle1.Cells(7 + i, 6) = Application.WorksheetFunction.VLookup(Tabelle2.Cells(i, 1), myrange, 3, False)
Next i


Dim lastrow2 As Long
lastrow2 = Tabelle3.Range("A" & Rows.Count).End(xlUp).Row
Set myrange2 = Tabelle3.UsedRange

For i = 2 To lastrow2
    Tabelle1.Cells(7 + i, 2) = Application.WorksheetFunction.VLookup(Tabelle1.Cells(i, 1), myrange2, 2, False)
Next i

For i = 2 To lastrow2
    Tabelle1.Cells(7 + i, 4) = Application.WorksheetFunction.VLookup(Tabelle1.Cells(i, 1), myrange2, 3, False)
Next i

For i = 2 To lastrow2
    Tabelle1.Cells(7 + i, 5) = Application.WorksheetFunction.VLookup(Tabelle1.Cells(i, 1), myrange2, 4, False)
Next i

For i = 2 To lastrow2
    Tabelle1.Cells(7 + i, 7) = Application.WorksheetFunction.VLookup(Tabelle1.Cells(i, 1), myrange2, 5, False)
Next i

End Sub

推荐答案

我认为问题在于您引用工作表的方式.您正在使用Worksheet.CodeName与Worksheet.Name的工作表.

I think the problem is the way you are referencing your worksheets. You are using the Worksheet.CodeName vs the Worksheet.Name of the worksheet.

看下面的示例，您将看到Worksheet.CodeName和Worksheet.Name不匹配.

Look at my example below and you will see that the Worksheet.CodeName and Worksheet.Name do not match.

Worksheet.CodeName是名称的第一部分，Worksheet.Name是括号中显示的内容.因此，第二个工作表的Worksheet.CodeName为Sheet5，而Worksheet.Name为Sheet6.

Worksheet.CodeName is the 1st part of the name and Worksheet.Name is what's shown in parentheses. Therefore the Worksheet.CodeName for the second worksheet is Sheet5, whereas the Worksheet.Name is Sheet6.

这是因为我删除了一个工作表，而excel在幕后将其重命名为Worksheet.CodeName引用.

This is because I deleted a worksheet and excel, behind the scenes, renamed the Worksheet.CodeName reference.

要使用在查看工作簿中的选项卡时看到的内容，需要通过Worksheet.Name而不是Worksheet.CodeName引用它.

To use what you see when looking at the tabs in the workbook you need to reference it by Worksheet.Name, not Worksheet.CodeName.

Sub testPickingWorksheets()

' This code fails
    a = Sheet6.Range("A1").Value
    MsgBox (a)

' This code works
    a = Worksheets("Sheet6").Range("A1").Value
    MsgBox (a)

End Sub

从上面的代码中可以看到，您需要对Worksheet.Name使用引号"中的Worksheets()对象，而不是直接引用Worksheet.CodeName.

As you can see from the code above, you need to use the Worksheets() Ojbect with the Worksheet.Name in "quotes" instead of directly referencing the Worksheet.CodeName.

这篇关于合并两个工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！