VBA vlookup to copy specific columns from multiple sheets to master sheet

Question

I am using Excel to produce reports for a billing system and I would like to use VBA to simplify the process of updating the excel. What I want to do is to use vlookup function to reflect columns (G:AI) from respective Named worksheets back to mastersheet. All sheets starts from row 4. (Row 3 is header)

So I'll further simplify the process as such:

VLOOKUP VBA (When changes made in the Named worksheets) 1. To enable Vlookup function in column (G:AI) in Mastersheet from Named worksheets ("John", "Charlie", "George") 2. As Mastersheet is a mixed data of John, Charlie and George, to input Vlookup formulas across column (G:AI) accordingly, then till last row of Mastersheet 3. My vlookup range will be from Named worksheets (John, Charlie, George), range (A1:AI) starting from column 7, row 4 till the end of the data.

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

Here are the codes I have so far. It's all I worked out (with help) as of now. Any help would be greatly appreciated.

My issue is, when running the code, vlookup values for ws11 is in the right place. However, vlookup values for ws12 and ws13 are shifted towards further left of the worksheet. For example, while vlookup values for ws11 is in columns (A:AI) - the right columns vlookup values for ws12 is in columns (AP:BR) - 7 columns from column AI and vlookup values for ws13 is in columns (BY:DA) - 7 columns from column BR Is there a line of code that I can insert to fix this?

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

I'm making an assumption that this is falling over due to colnum not being set before it is used in the vlookup — Smithy7876
@SJR I have edited the question for better understanding. My apologies for the delay — Carmen
@smithy7876 I think the colnum is okay cause I needed it to run in different columns as it run. Now it's the alignment of the vlookup values in ws12 and ws13 respectively that has an issue. Thank you. :) — Carmen
At the moment you have hard-coded wsNames.Range("A1:AI500"). Are you saying that this only applies to the first sheet in your loop and that for each of the others it should be shifted right as you have indicated? — SJR

SJR SJR · Accepted Answer · 2017-05-26T13:40:55

I honestly can't see what can be causing the problem you describe based on the code posted. There is nothing substantially different in the code below - I have tidied up a couple of the loops and incorporated the last row variable. Let me know how you get on.

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

VBA vlookup to copy specific columns from multiple sheets to master sheet

1 Answers