Copy cell value based on multiple criteria

[cepinac]

Hi everyone, I've been trying to figure out this by myself but it's out of my reach.

Quick description:
Sheet "Sales" contains 3 columns:
Customer Name | Product | Qty
Workbook contains another 10 sheets with different customer names (same as "Sales" Customer Name).

Sheet "Customer XYZ" contains multiple colums but key one is Product (with matching name as sheet "Sales").

I would like to copy Qty of matching Customer and Product then paste it in an appropriately named sheet.

i.e.
Sales sheet:
Customer XYZ | Oranges | 10
copy 10 and paste it to a Specified colum (i.e. N) and a row containing Oranges in sheet Customer XYZ.

Thank you

 

[davesexcel]

A bit confusing, you say sheet "customerxyz" but your sheet names are the product names.

 

[cepinac]

A bit confusing, you say sheet "customerxyz" but your sheet names are the product names.

Hi Dave, sorry my mistake I was required to make a dummy example of real sheet here is correct look:

 

[davesexcel]

Were you looking for a macro or a formula?

 

[cepinac]

Were you looking for a macro or a formula?

hi, macro would be easier

 

[davesexcel]

If you upload a sample to workbook to a shared drive, then supplied a link, it would be a great help.

 

[cepinac]

If you upload a sample to workbook to a shared drive, then supplied a link, it would be a great help.

sure, here you go. btw data can be pasted into any column i can adjust accordingly afterwards

thx for helping

example_sheet.xlsx

1 file sent via WeTransfer, the simplest way to send your files around the world

we.tl

 

[davesexcel]

try this.

Sub Send_It_Man()
    Dim sh As Worksheet, ws As Worksheet
    Dim rng As Range, c As Range, fRng As Range

    Set sh = Sheets("Sell_Out")
    With sh
        Set rng = .Range("A2:A" & .Cells(.Rows.Count, "A").End(xlUp).Row)
    End With

    For Each c In rng.Cells
        Set fRng = Sheets(c.Value).Range("A:A").Find(what:=c.Offset(, 1), lookat:=xlWhole)
        If Not fRng Is Nothing Then
            fRng.Offset(, 1) = c.Offset(, 2)

        End If
    Next c
    
End Sub

 

[cepinac]

try this.

Sub Send_It_Man()
    Dim sh As Worksheet, ws As Worksheet
    Dim rng As Range, c As Range, fRng As Range

    Set sh = Sheets("Sell_Out")
    With sh
        Set rng = .Range("A2:A" & .Cells(.Rows.Count, "A").End(xlUp).Row)
    End With

    For Each c In rng.Cells
        Set fRng = Sheets(c.Value).Range("A:A").Find(what:=c.Offset(, 1), lookat:=xlWhole)
        If Not fRng Is Nothing Then
            fRng.Offset(, 1) = c.Offset(, 2)

        End If
    Next c
   
End Sub

thank you it works fast
how would you go about changing paste column?

 

[davesexcel]

There is not paste, you are making one cell equal another cell.

fRng.Offset(, 1)

For this example it's column B

fRng.Offset(, 2)

would be column C