VBA Vlookup Multiple Sheets Question

[carmenchongccc]

Hi, I would like to run a vlookup vba and have encountered the following problem. The idea is to vlookup columns G to AI from row 4 onwards for sheet 2-4 ( I have since classified the sheets under "mysheets") and insert them into master sheet (sheet1) from columns G to AI. The bil number is used as the identifier for the vlookup. I have came up with a simple vba code and am not too sure if it works. Would love some help making this code woork. Thank you so much for your help!

  Sub green_update()
       
    Dim bil As String
    Dim lastrow As Integer
    Dim for_col As Long, i As Long, r As Long, c As Long, colnum As Long
    r = 4: c = 7: colnum = 7
   
   
    For for_col = 1 To Range("AI3").End(xlToLeft).column
   
        For i = 1 To Range("A500").End(xlUp).row
        Cells(r, c).Value = WorksheetFunction.VLookup(Cells(r, 4).Value, Sheets("mysheets").Range("A1:AI500"), colnum, False)
         r = r + 1
        Next
       
     r = 4
     colnum = colnum + 1
     c = c + 1
     
    Next
   
    End Sub

[CODE]

Thank you!

 

[SirJB7]

Hi, carmenchongccc!

Welcome to Chandoo forums!
Checked this yet?
http://chandoo.org/forum/forums/new-users-please-start-here.14/

Consider uploading a sample file. It'd be easier to understand for people who might be able to help you.
Include a manually written output of the requested solution. Thanks.
Regards!