URGENT! Need a quick way to auto-fill some cells

Copper Contributor

When I enter the same product name, I need it to auto-fill some cells belonging to that product. 

For example yesterday I entered a product "Kero Domates Fidesi" as you can see in yellow. The numbers belonging to that product are the same like its buying price and selling price. So when I enter the same product today I want the program to fill automatically the buying price and selling price. I don't want to copy paste the cells all day. Can someone help me please? 

 

 

Ekran Alıntısı.PNG

5 Replies

@gamzeoz93 

Which column contains the product names?

Which column contains the buying price?

Which column contains the selling price?

I tagged them in black in the first bar @Hans Vogelaar 

I sent you a message because I can't load a picture here.

@gamzeoz93 

You haven't specified which columns (B, C, ...), so I'll assume that the product price is in column B.

Right-click the sheet tab.

Select 'view Code' from the context menu.

Copy the following code into the worksheet module:

Private Sub Worksheet_Change(ByVal Target As Range)
    Const ProductCol = "B"
    Dim cel As Range
    Dim cel2 As Range
    If Not Intersect(Columns(ProductCol).EntireColumn, Target) Is Nothing Then
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        For Each cel In Intersect(Columns(ProductCol).EntireColumn, Target)
            If cel.Value <> "" Then
                Set cel2 = Columns(ProductCol).Find(What:=cel.Value, After:=cel, LookAt:=xlWhole)
                If cel2.Address <> cel.Address Then
                    cel.Offset(0, 4).Value = cel2.Offset(0, 4).Value
                    cel.Offset(0, 5).Value = cel2.Offset(0, 5).Value
                End If
            End If
        Next cel
        Application.EnableEvents = True
        Application.ScreenUpdating = True
    End If
End Sub

Switch back to Excel.

Save the workbook as a macro-enabled workbook (*.xlsm).

Make sure that you allow macros when you open the workbook.

 

Thank you very much I will try