using vlookup on multiple sheets VBA (results in different columns)

cams88

New Member
Joined
Mar 23, 2020
Messages
21
Office Version
  1. 2016
Platform
  1. Windows
Hi,

I have multiple sheets with data and I'm trying to search data by using vlookup formula to main sheet "Dane". Results from vlookup should be in different columns for example: data from sheet1 should be in column B, data from sheet2 should be in colum c etc. I was thinking that I can do it with "a" variable (blue text color in macro) but this method doesn't work...

VBA Code:
Sub Dane_all ()

Sheets("Dane").Activate

Dim wks As Worksheet
Dim wkb As Workbook
Dim key As String
Dim rowIndex As Integer
Dim wksName As Variant
Dim LastRow As Integer


Set wkb = ThisWorkbook

With wkb.Sheets("Dane")
    LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
End With
[COLOR=rgb(41, 105, 176)] a = 1[/COLOR]
' for each name in input sheet's column- A search the name in all sheets other then input sheet
For rowIndex = 2 To LastRow Step 1
  key = wkb.Worksheets("Dane").Cells(rowIndex, a).Value ' name to search
    For Each wks In wkb.Worksheets ' loop through all the sheets
        If Not wks.Name = "Dane" Then ' avoid searching in input sheet itself
          On Error Resume Next ' to search next sheet when not found in current one
          wkb.Worksheets("Dane").Cells(rowIndex, [COLOR=rgb(41, 105, 176)]a + 1[/COLOR]).Value = Application.WorksheetFunction.VLookup(key, wks.Range("A:B"), 2, False)
       End If
    Next wks
Next rowIndex


End Sub
 
Last edited by a moderator:

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,111
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
Sub Dane_all()

Sheets("Dane").Activate

Dim wks As Worksheet
Dim wkb As Workbook
Dim key As String
Dim rowIndex As Integer
Dim wksName As Variant
Dim LastRow As Integer
Dim x As Variant

Set wkb = ThisWorkbook

With wkb.Sheets("Dane")
    LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
End With
a = 1
' for each name in input sheet's column- A search the name in all sheets other then input sheet
   For Each wks In wkb.Worksheets ' loop through all the sheets
      If Not wks.Name = "Dane" Then ' avoid searching in input sheet itself
         a = a + 1
         For rowIndex = 2 To LastRow Step 1
            key = wkb.Worksheets("Dane").Cells(rowIndex, 1).Value ' name to search
            x = Application.VLookup(key, wks.Range("A:B"), 2, False)
            If Not IsError(x) Then
               wkb.Worksheets("Dane").Cells(rowIndex, a).Value = x
            End If
         Next rowIndex
      End If
   Next wks
End Sub
 

cams88

New Member
Joined
Mar 23, 2020
Messages
21
Office Version
  1. 2016
Platform
  1. Windows
It works! Thank You very much Fluff :)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,111
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
How about

VBA Code:
Sub Dane_all()
  Dim c As Range, f As Range, sh As Worksheet
  For Each c In Sheets("Dane").Range("A2", Sheets("Dane").Range("A" & Rows.Count).End(3))
    For Each sh In Sheets
      Set f = sh.Cells.Find(c.Value, , xlValues, xlWhole)
      If Not f Is Nothing Then c.Offset(, 1).Value = f.Offset(, 1).Value
    Next sh
  Next c
End Sub

Edit:
Too late ...
 

Watch MrExcel Video

Forum statistics

Threads
1,113,979
Messages
5,545,316
Members
410,676
Latest member
M0J0jojo
Top