bring data from sheet to another based on cell value

leap-out · Jan 16th 2021

hello guys

I hope some body help to write macro the idea is when I write in cell value "e5" in sheet " invoice" based on cell value in column b in sheet "data then bring all of data in cells value in G5 & E8 and the rests of data from row 16 in sheet" invoice" I put the expected result in sheet "invoice"

this my try to write macro may be see this is not logic honestly I 'm beginner in vba but I try learning how write the code

Code

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("e5")) Is Nothing Then
Dim ws As Worksheet
lr = Cells(Rows.Count, 9).End(xlUp).Row
Set ws = Sheets("data")
With ws
If Target.Value = .Range("b" & lr) Then
Target.Range("g5") = .Range("a" & lr)
Target.Range("e8") = .Range("b" & lr)
Target.Cells(16, "b") = .Range("c" & lr)
Target.Cells(16, "c") = .Range("d" & lr)
Target.Cells(16, "d") = .Range("e" & lr)
Target.Cells(16, "e") = .Range("f" & lr)
Target.Cells(16, "f") = .Range("g" & lr)
Target.Cells(16, "g") = .Range("h" & lr)
Target.Cells(16, "i") = .Range("f" & lr)
End If
End With
End If
End Sub

Display More

**Carim** · Jan 16th 2021

Hello,

As an initial step, you could test following :

Code

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("E5")) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub
Dim ws As Worksheet
Dim lr As Long, res As Variant

Set ws = Sheets("data")
lr = ws.Cells(Rows.Count, 1).End(xlUp).Row

res = Application.Match(Target.Value, ws.Range("B1:B" & lr), 0)
If IsError(res) Then MsgBox "No match found": Exit Sub

With Target
    .Offset(0, 2).Value = ws.Cells(res, 1).Value
    .Offset(3, 0).Value = ws.Cells(res, 3).Value
End With

End Sub

Display More

Hope this will help

leap-out · Jan 16th 2021

thanks carim , but this doesn't work at all and no error also my code I thought may be disabled worksheet change event , I enabled by this code this Application.EnableEvents = True but doesn't work , any suggestion ,please?

**Carim** · Jan 16th 2021

Sorry ... but ... at my end, macro is working fine ...

See you attached test file ...

Hope this will help

leap-out · Jan 16th 2021

may you complete your work , please ? how about data show from row 16 in sheet invoice as in my result and I would see the whole code and I check it with you to understand how works

thanks again

**Carim** · Jan 16th 2021

Quote from leap-out

thanks carim , but this doesn't work at all and no error also my code I thought may be disabled worksheet change event , I enabled by this code this Application.EnableEvents = True but doesn't work , any suggestion ,please?

Do we agree the core event macro works ...???

leap-out · Jan 16th 2021

yes , we agree now the macro works but if you mean this is my question absolutely no you can check my post#1 what I try and from the beginning I put the result in sheet invoice if this is not clear I'm ready to explain more but you don't ask me

**Carim** · Jan 16th 2021

Now that the core event macro operates fine ...

You do have two possibilities :

1. Fill in the Invoice details with the formulas to retrieve all the details based on the input cell E5

or

2. With VBA, learn to filter data and copy it back to the Invoice sheet

Hope this will help

leap-out · Jan 16th 2021

thanks

I would that by vba not formula as you see I try by vba and I failed ,that's why I ask assistance to complete my project and see how the code works

thanks

**Carim** · Jan 16th 2021

Since you are in a learning process ...

In order to design a VBA solution to Filter and copy filtered rows back to your sheet ... take a look at following thread :

copy rows with filter based on values in another sheet

Hope this will help

leap-out · Jan 16th 2021

it's huge codes and hard to deal whit theses codes, is there norther way to do that ?

**Carim** · Jan 16th 2021

Take a look at following your Version 2 test file ...

**Carim** · Jan 17th 2021

It would appear that you have downloaded the proposal ...

Feel free to share your comments ...

leap-out · Jan 17th 2021

great work ! honestly I was expected doing that by vba , it doesn't seem easy , despite you did it by formula but it's very impressive thanks for your time and patience

**Carim** · Jan 18th 2021

Thanks for your Thanks ... AND for the Like

Mumps · Jan 18th 2021

Macro approach:

Delete all the formulae in columns B to G. Place this macro in the worksheet code module. Enter an Invoice number in E5 and press the RETURN key or TAB key.

Code

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    Application.ScreenUpdating = False
    Dim srcWS As Worksheet, lRow As Long
    Set srcWS = Sheets("DATA")
    If Target.Address(0, 0) = "E5" Then
        With srcWS
            If WorksheetFunction.CountIf(.Range("B:B"), Target.Value) <> 0 Then
                lRow = Range("B" & Rows.Count).End(xlUp).Row + 1
                .Range("A1").AutoFilter 2, Target.Value
                .Range("A1").AutoFilter 3, Target.Offset(3).Value
                .Range("D2", .Range("I" & .Rows.Count).End(xlUp)).SpecialCells(xlCellTypeVisible).Copy Range("B" & lRow)
                .Range("A1").AutoFilter
            Else
                MsgBox "No match found for " & Target.Value & "."
                Exit Sub
            End If
        End With
    End If
    Application.ScreenUpdating = True
End Sub

Display More

leap-out · Jan 18th 2021

hi mumps ,there is something wrong it brings the headers topic !

Mumps · Jan 18th 2021

Try the attached file. It's working properly for me.

leap-out · Jan 18th 2021

very strange from the first time it works but if I select another number in e5 it returns the same problem you can see what I get in my file

leap-out · Jan 18th 2021

should see this

bring data from sheet to another based on cell value

Participate now!

Share