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:

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
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
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0
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 ...
 
Upvote 0

Forum statistics

Threads
1,213,496
Messages
6,113,993
Members
448,539
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top