200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
Hello
in my file should just brings data based on helper column J for OUTPUT sheet when match items in column B for the others sheets , but I note to brings all of data from multiple sheets .
so the higlighted by red should not brings because they are not existed in helper column J in OUTPUT sheet .
any idea to fix that ?
Ali
I think you just need to move one line from the outer loop (using counter i) to the inner loop (j) i.e. only increment variable k and write a new line once you've found a match with List. See changes in bold:
Option Explicit
Sub test()
Dim lr&, i&, j&, k&, rng, list, res(1 To 100000, 1 To 4), ws As Worksheet
With Sheets("OUTPUT")
list = .Range("J2:J" & .Range("J" & Rows.Count).End(xlUp).Row)
For Each ws In Sheets
If ws.Name <> "OUTPUT" Then
lr = ws.Cells(Rows.Count, "A").End(xlUp).Row
rng = ws.Range("A2:D" & lr).Value
For i = 1 To UBound(rng)
'k = k + 1: res(k, 1) = rng(i, 1): res(k, 2) = rng(i, 2): res(k, 3) = rng(i, 3): res(k, 4) = rng(i, 4)
For j = 1 To UBound(list)
If rng(i, 2) Like list(j, 1) & "*" Then
k = k + 1: res(k, 1) = rng(i, 1): res(k, 2) = rng(i, 2): res(k, 3) = rng(i, 3): res(k, 4) = rng(i, 4)
res(k, 2) = list(j, 1)
Exit For
End If
Next
Next
End If
Next
.Range("A2:D100000").ClearContents
.Range("A2").Resize(k, 4).Value = res
.Range("A2").CurrentRegion.Borders.LineStyle = xlContinuous
.Range("A2").CurrentRegion.Sort key1:=.Range("B1"), Header:=xlYes
End With
End Sub
Also, I note that your List has spelling errors (ACRRUED and BYUING) which (once your current data matches) should be corrected to read:
ACCRUED REVENUE
ACCRUED EXPENSE
BUYING
EXPORTING
PREPAID REVENUE
PREPAID EXI have NOT done that in the attached revised file.
Hope this fixes your problem. If so, please mark this Answer as Selected.