Excel VBA Course
Excel VBA Course - From Beginner to Expert

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

View Course

copy data from sheets based on helper column

0

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 ?

Answer
Discuss

Answers

0
Selected Answer

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 EX

I have NOT done that in the attached revised file.

Hope this fixes your problem. If so, please mark this Answer as Selected.

Discuss

Discussion

Hi John,
sorry  about spelling errors  !
thank  you  so  much  for  your assistance .
Ali M (rep: 28) Aug 26, '23 at 3:44 pm
No need to apologise, Ali - we all make mistakes. Glad that worked, thanks for selecting my Answer 
John_Ru (rep: 6212) Aug 26, '23 at 5:07 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login