Values of cells to be routed to other sheet

Dear All,

Please refer the attached excel Sheet1 Columns F & G

Location is a dropdownlist where I've made search suggestion with formulas

Dispatch Type is another dropdownlist with only 2 values

Model is a custom data validation formula which minimizes the values of F14 to F48 i.e., If I type 600 in G10, it will display values of only those values which contains 600 from F14 to F48

Requirement of Quantity button:

1. When I select location, Dispatch Type & particular cells in F14 to F48, Enter quantity in G14 to G48 (based on the values minimized in F14 to F48), Those quantities or values to be routed to SSR Sheet to the particular cells

2. Message box has to be displayed with models that I selected and total quantity of those models after summing up the values

3. if you have any thoughts with better improvements, Please do & let me know. But not manditory

Request you to please help on the above requirement and do the needful.

Dear All,

Could someone reply on this.

1 try

this in sheet_change

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$G$9" Or Target.Address = "$G$8" Then
    my_loc = Range("G8")
    my_disp = Range("G9")
    With Sheets("SSR")
        lcol = .UsedRange.Columns.Count
        For x = 2 To lcol
            If .Cells(5, x) = my_loc And .Cells(6, x) = my_disp Then
                my_col = x
                Exit For
            End If
        Next
        .Range(.Cells(8, my_col), .Cells(42, my_col)).Copy
    End With
    With Sheets("sheet1").Range("H14")
        .PasteSpecial Paste:=xlValues
    End With
End If
End Sub

this in quantity add

Sub Macro1()
    my_loc = Range("G8")
    my_disp = Range("G9")
    With Sheets("SSR")
        lcol = .UsedRange.Columns.Count
        For x = 2 To lcol
            If .Cells(5, x) = my_loc And .Cells(6, x) = my_disp Then
                my_col = x
                Exit For
            End If
        Next
    End With
    For Each cl In Range("H14", "H48")
        cl.Value = cl.Value + cl.Offset(, -1).Value
    Next
    Range("H14", "H48").Copy
    With Sheets("SSR").Cells(8, my_col)
        .PasteSpecial Paste:=xlValues
    End With
    Range("G14", "H48").ClearContents
End Sub

made also 2 named ranges for region and dispatch on extra sheet (blad1)

Kind regards
Leo

not sure, maybe so is better


Kind regards
Leo

Dear Leo,

Could you please help me out regarding this.

now it add the quantity

i made a small change in your index formula to make it work corect.


Kind regards
Leo

Thanks Leo!!!! As usually I say everytime you implement in an innovative way!!!!