VBA Code for Vlookup every sheet in Workbook?

[TheLSD]

Hi guys, I need the help of vlookup code for selected cells through the worksheet on the workbook. So I have a column of data series from different sheets and I need to vlookup through the sheet over the workbook for the next column for each selected column. Any ideas of what the code is?
Any help is appreciated, Thank you!

 

[offthelip]

that is a really vague question, I think you need to be more specific, i.e
1: what is the name or names of the sheet/sheets which you want to look up the data on?
2: what is the range of columns this data extends over
3; which column is to be used to match the data
4: which column to you want returned in the vlookup
5: which sheet do you want the results returned on
6: which range do you want the results retruned to
7: if there are more than one sheet that you wnat to look up data on , is there and easy way to identify the sheets, e.g. all sheets except MASTER

 

[TheLSD]

that is a really vague question, I think you need to be more specific, i.e
1: what is the name or names of the sheet/sheets which you want to look up the data on?
2: what is the range of columns this data extends over
3; which column is to be used to match the data
4: which column to you want returned in the vlookup
5: which sheet do you want the results returned on
6: which range do you want the results retruned to
7: if there are more than one sheet that you wnat to look up data on , is there and easy way to identify the sheets, e.g. all sheets except MASTER

1. let's say that the name of the sheets are A,B,C,D, and so on (without a specific amount number of sheets), and the arrangement is not always in the position
2. approx. 15
3. I use column c
4. same c column
5. lets say "master sheet"
6.
7. the name of the sheets perhaps, there're always different so won't be easily mistaken.

nb: The master sheet layout is different from the datasheets, but all the data have the same layout

 

[offthelip]

try this code it is not using vlookup it is using variant arrays but it does the same job as vlookup but is much much faster and far more flexible because you can copy multiple columns at once, I am copying all 15 columns because you haven't given more details. This code will be very fast even if you have thousands of lines of data to match.

Sub test()
shtnames = Array("A", "B", "C")
With Worksheets("Mastersheet")
lastrow = .Cells(Rows.Count, "C").End(xlUp).Row
inarr = .Range(.Cells(1, 3), .Cells(lastrow, 19)) ' pick up range C1 to S lastrow
End With
  For nm = 0 To UBound(shtnames)
   With Worksheets(shtnames(nm))
   lastdata = .Cells(Rows.Count, "C").End(xlUp).Row
   datarr = .Range(.Cells(1, 3), .Cells(lastrow, 18)) ' pick up range C1 to R lastrow
      For i = 1 To lastrow
        For j = 1 To lastdata
         If inarr(i, 1) = datarr(j, 1) Then ' we have found a match copy 15 columns of data
          For k = 2 To 15
            inarr(i, k) = datarr(j, k)
          Next k
          Exit For
         End If
        Next j
      Next i
    End With
   Next nm
With Worksheets("Mastersheet")
 .Range(.Cells(1, 3), .Cells(lastrow, 19)) = inarr
End With
    
End Sub

 

[TheLSD]

I try to run for the code after some adjustment of the sheet name and when I try to run the code, error code 9 out of range pops out. I thought that its because of the range and I try to change the range but still run the same pop out
any fix for this?
I underline the row that the error comes from
here's the code after some adjustment

For nm = 0 To UBound(shtnames)
   With Worksheets(shtnames(nm))
   lastdata = .Cells(Rows.Count, "C").End(xlUp).Row
   datarr = .Range(.Cells(1, 3), .Cells(lastrow, 18)) ' pick up range C1 to R lastrow
      For c = 1 To lastrow
        For j = 1 To lastdata
        [U] If inarr(c, 1) = datarr(j, 1) Then ' we have found a match copy 15 columns of data[/U]
          For k = 1 To 15
            inarr(c, k) = datarr(j, k)
          Next k
          Exit For
         End If
        Next j
      Next c
    End With
   Next nm
With Worksheets("Sheet4")
 .Range(.Cells(1, 3), .Cells(lastrow, 19)) = inarr
End With
   
End Sub

 

[offthelip]

You seem to have missed some code out of your post at the start of the sub, can you post all of your code please

 

[TheLSD]

here's the code that I tweak

Sub test()
shtnames = Array("IT", "NPD")
With Worksheets("Sheet4")
lastrow = .Cells(Rows.Count, "C").End(xlUp).Row
inarr = .Range(.Cells(1, 3), .Cells(lastrow, 19)) ' pick up range C1 to S lastrow
End With
  For nm = 0 To UBound(shtnames)
   With Worksheets(shtnames(nm))
   lastdata = .Cells(Rows.Count, "C").End(xlUp).Row
   datarr = .Range(.Cells(1, 3), .Cells(lastrow, 18)) ' pick up range C1 to R lastrow
      For c = 1 To lastrow
        For j = 1 To lastdata
         If inarr(c, 1) = datarr(j, 1) Then ' we have found a match copy 15 columns of data
          For k = 2 To 15
            inarr(c, k) = datarr(j, k)
          Next k
          Exit For
         End If
        Next j
      Next c
    End With
   Next nm
With Worksheets("Sheet4")
 .Range(.Cells(1, 3), .Cells(lastrow, 19)) = inarr
End With
    
End Sub

 

[offthelip]

Change this line
datarr = .Range(.Cells(1, 3), .Cells(lastrow, 18)) ' pick up range C1 to R lastrow
To

datarr = .Range(.Cells(1, 3), .Cells(lastdata, 18)) ' pick up range C1 to R lastrow


Sorry my mistake