Copy and Paste if a criteria is met (1 Viewer)

[VGK]

We have a requisition form on sheet 1 and then sheets 2-5 are of different products.
What I'm trying to do is find a way that if someone enters a qty into sheets 2-5 that it copies relevant information into our requisition form (see attached).

I've seen copy/past where the data already exists, but nothing for when a # is entered

 

[Darrell]

This might get you started. You'll need to put this in the Worksheet_Change of each sheet 2-5

Private Sub Worksheet_Change(ByVal Target As Range)

Dim wReq_Sheet As Worksheet
Dim sMat As String
Dim iQty As Integer
Dim sDesc As String
Dim iLastRow As Integer

Set wReq_Sheet = ThisWorkbook.Sheets("Sheet1")      ' Or whatever your 'Requisition form' sheet is called

If Target.Column = 2 Then
    sMat = Range("A" & Target.Row)
    iQty = Target.Value
    sDesc = Range("C" & Target.Row)
    
    With wReq_Sheet
        If iQty > 0 Then
            iLastRow = .Range("A50").End(xlUp).Row + 1   ' I chose 50 rows, you'll want the row directly above 'Notes & Instructions'
            .Range("A" & iLastRow) = sMat
            .Range("B" & iLastRow) = sDesc
            .Range("C" & iLastRow) = iQty
        End If
    End With
    
End If

Set wReq_Sheet = Nothing

End Sub

 

[Isaac]

nothing against what darrell entered because frankly i love making use of the worksheet change event myself, but is this a case where you might also be able to put vlookups (or index/match, if your source data isn't in the perfect positions for vlookup) - and then tell the people "it will autofill, but you can override it if you want" ?

 

[VGK]

nothing against what darrell entered because frankly i love making use of the worksheet change event myself, but is this a case where you might also be able to put vlookups (or index/match, if your source data isn't in the perfect positions for vlookup) - and then tell the people "it will autofill, but you can override it if you want" ?

I'm familiar with vlookups but how to do look up a row to see if it has data to copy to another spreadsheet?

 

[Isaac]

Your requirements as stated are extremely vague, so I'm just guessing, really. Only if the source data can be matched in some way to the lookup value, then it would work. But just to say "look up a row to see if it has data" - that's kinda vague, so I'll bow out now - that was just an idea.

 

[Pat Hartman]

I'm sure you're happy with Excel but perhaps you should think about turning this into a database app using Access.