Editing something via macro?

[Antonow]

Hello everyone, It´s been a long time since i´ve been here! and i need your help one more time.

I´m developing a spreadsheet where i have to input something like the following.

Mold	Model	Comp 1	Quant 1	Comp 2	Quant 2	Comp 3	Quant 3	Comp 4	Quant 4	Status	Data

<tbody>
</tbody>

I have 2 worksheets, one labeled Input and another one labeled Vorlagen.

I managed to make the input macro, where it gets the information from Input, places it in Vorlagen and clear the information from the input worksheet, here is the code:

Sub Vorlagen_Input()

    'Sheets("Input").Unprotect Password:="Verboten"
    'Sheets("Vorlagen").Unprotect Password:="Verboten"


    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Dim NextRow As Long


    Set ws1 = ThisWorkbook.Worksheets("Input")
    Set ws2 = ThisWorkbook.Worksheets("Vorlagen")


    With ws2
    
        NextRow = .Cells(.Rows.Count, "B").End(xlUp).Row + 1
        .Cells(NextRow, "B") = ws1.Cells(4, "B").Value  ' Schimmel
        .Cells(NextRow, "C") = ws1.Cells(4, "C").Value  ' Modell
        .Cells(NextRow, "D") = ws1.Cells(4, "D").Value  ' Komponente 1
        .Cells(NextRow, "E") = ws1.Cells(4, "E").Value  ' Anzahl 1
        .Cells(NextRow, "F") = ws1.Cells(4, "F").Value  ' Komponente 2
        .Cells(NextRow, "G") = ws1.Cells(4, "G").Value  ' Anzahl 2
        .Cells(NextRow, "H") = ws1.Cells(4, "H").Value  ' Komponente 3
        .Cells(NextRow, "I") = ws1.Cells(4, "I").Value  ' Anzahl 3
        .Cells(NextRow, "J") = ws1.Cells(4, "J").Value  ' Komponente 4
        .Cells(NextRow, "K") = ws1.Cells(4, "K").Value  ' Anzahl 4
        .Cells(NextRow, "L") = ws1.Cells(4, "L").Value  ' Status
        .Cells(NextRow, "M") = ws1.Cells(4, "M").Value  ' Daten


    'Sheets("Input").Protect Password:="Verboten"
    'Sheets("Vorlagen").Protect Password:="Verboten
    
    End With
    
    Range("B4:M4").Select
    Selection.ClearContents
    Range("B4").Select

What i'd like to do is, have a worksheet or a button in the Input worksheet, that when you select a Mold from a dropdown list, it gets the information of that one and if you need to change lets say.. its status, you change and press "Update" and it will update it in the Vorlagen worksheet. I've been hitting my head against the keyboard for hours and I can't seem to make it work. Can you guys help?

Btw, i'm sorry for any grammatical error.. It's been a while since i've used English.

 

[steve the fish]

If ive read correctly you need this part:

With ws2

to be dependant on the dropdown cell value? Is that correct?

 

[steve the fish]

Oh i think i get it now.... give me a few minutes

 

[steve the fish]

Ok. Change the first part to the sheet and cell reference of your dropdown and change 'update' to whatever you say in the cell then you can set your variable like this:

If Sheets("Sheet1").Range("A1").Value = "update" Then
    NextRow = Application.Match(ws1.Cells(4, "C").Value, ws2.Columns("C"), 0)
Else
    NextRow = ws2.Cells(Rows.Count, "B").End(xlUp).Row + 1
End If

If IsError(NextRow) Then
    MsgBox "There is no match found. You cannot update"
    Exit Sub
End If

 

[Antonow]

Worked! Thanks