VBA vlookup将特定列从多个工作表复制到主表

Question

我正在使用excel为计费系统生成报告，我想使用VBA来简化更新Excel的过程。我想要做的是使用vlookup函数将列(G:AI)从各自的命名工作表反射回母版。所有工作表都从第4行开始。(第3行是标题)

因此，我将进一步简化这个过程：

1.从命名工作表(" John“、" Charlie”、" George ") 2中启用Mastersheet中列(G:AI)中的vlookup函数。由于Mastersheet是John、Charlie和George的混合数据，因此可以跨列(G:AI)输入Vlookup公式，直到Mastersheet 3的最后一行。我的Vlookup范围将从命名工作表(John、Charlie、George)开始，范围(A1：)AI从第7列开始，第4行一直到数据的末尾。

ws1.Cells(r, c).Value = Application.VLookup(ws1.Cells(r, 1).Value, wsNames.Range("A1:AI500"), colnum, False)

这是我到目前为止的密码。到目前为止，这就是我(在帮助下)所做的全部工作。任何帮助都将不胜感激。

我的问题是，在运行代码时，ws11的vlookup值位于正确的位置。但是，ws12和ws13的vlookup值将移到工作表的更左边。例如，虽然ws11的vlookup值位于列(A: AI ) -- ws12的正确列vlookup值位于列(AP: BR ) --来自列AI的vlookup值在列中，而ws13的vlookup值位于列(BY:DA) -列BR的列中。

Sub green_update()
Dim wb As Workbook, ws1 As Worksheet, ws11 As Worksheet, ws12 As Worksheet, ws13 As Worksheet

Set wb = ThisWorkbook
Set ws1 = wb.Sheets("Sheet1")
Set ws11 = wb.Sheets("Sheet11")
Set ws12 = wb.Sheets("Sheet12")
Set ws13 = wb.Sheets("Sheet13")


Dim colNo As Long, ARowNo as Long
Dim for_col As Long, i As Long, r As Long, c As Long, colnum As Long
r = 4: c = 7: colnum = 7

Dim wsNames As Variant
For Each wsNames In Sheets(Array("sheet11", "sheet12", "sheet13"))

colNo = wsNames.Cells("4", Columns.Count).End(xlToLeft).Column
 For for_col = 1 To colNo

ARowNo = wsNames.Cells(Rows.Count, "A").End(xlUp).row

    For i = 1 To ARowNo
    ws1.Cells(r, c).Value = Application.VLookup(ws1.Cells(r, 1).Value, wsNames.Range("A1:AI500"), colnum, False)
    If IsError(ws1.Cells(r, c).Value) Then
    ws1.Cells(r, c).Value = 0
    End If
    r = r + 1

    Next

 r = 4
 colnum = colnum + 1
 c = c + 1

Next

colnum = 7 

  Next wsNames

End Sub   

Answer 1

老实说，我看不出是什么导致了您根据发布的代码所描述的问题。下面的代码没有本质上的不同--我整理了几个循环并合并了最后一个行变量。告诉我你进展如何。

Sub green_update()

Dim wb As Workbook, ws1 As Worksheet

Set wb = ThisWorkbook
Set ws1 = wb.Sheets("Sheet1")

Dim colNo As Long, ARowNo As Long
Dim for_col As Long, i As Long, r As Long, c As Long, colnum As Long

r = 4: c = 7: colnum = 7

Dim wsNames As Variant

For Each wsNames In Sheets(Array("sheet11", "sheet12", "sheet13"))
    colNo = wsNames.Cells("4", Columns.Count).End(xlToLeft).Column
    ARowNo = wsNames.Cells(Rows.Count, "A").End(xlUp).Row
    For for_col = 1 To colNo
        For i = 1 To ARowNo
            ws1.Cells(r, c).Value = Application.VLookup(ws1.Cells(r, 1).Value, wsNames.Range("A1:AI" & ARowNo), colnum, False)
            If IsError(ws1.Cells(r, c).Value) Then
                ws1.Cells(r, c).Value = 0
            End If
            r = r + 1
        Next i
        r = 4
        colnum = colnum + 1
        c = c + 1
    Next for_col
    colnum = 7
Next wsNames

End Sub