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...
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: