Questions on automating an order form

[claytonef]

Like many others, I completely lack VBA knowledge but would really like to start learning as it will ultimately make my workflow so much more efficient. Unfortunately, I'm working with something a bit time sensitive so I'm not being very efficient trying to do so much on my own.

What I'd like, is to have an order form that populates it's cells based on what cells were double clicked on in the combined lookup / input sheet, but also have that be cross-referenced with an inventory. Like so:

Sheet 1 (Inventory) - Populates the 'Quantity' for the Input ("NotGo") sheet in column G, uses backwards compatibility w/"NotGo"

Sheet 2 & 3 will eventually need to be combined, but I can do so later. They're named 'Go' (2) and 'NotGo' (3) and will be used for Input

Sheet 4 (OrderForm) - Populated order form to be sent to vendor.

I'd like the Input sheet (NotGo) to have a macro enabled where someone can click on the 'N.G.PN' in the table in Column A and have that populate cell A2 on the same sheet. The first 3 rows are frozen to allow users to search without having to scroll back up. Once cell A2 is populated, a user will enter the quantity, and click 'Purchase' (not a functioning button currently), which will then export the corresponding cells in the table (A5:G836) to cells H4:M4 in the "OrderForm" sheet.

Ideally, there would also be a macro on the Input sheet that will prompt users to either use an item from the inventory, or if they would still want to place the order anyways. Dependent on the answer, the quantity of inventory would be decreased.

If anyone can help, that'd be greatly appreciated.

Much thanks!

 

[Logit]

Option Explicit
'Here is enough for you to get started and finish on your own.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim sh2 As Worksheet        ' Pointer to the seet in the other workbook
Dim LRow As Long
Dim LRow2 As Long
Dim i As Long
Cancel = True

Set sh2 = Sheets(2) ' you said you wanted to look at the second sheet. sh2 now "knows"

LRow = Sheets(1).Range("C" & Rows.Count).End(xlUp).Row
Target.Cells(i + 1, 1).EntireRow.Copy

LRow2 = sh2.Range("C" & Rows.Count).End(xlUp).Row
For i = 1 To LRow2
    If sh2.Cells(i, "C") = "" Then
        Exit For
    End If
Next i

' Paste the previously copied contents of the second row in the original workbook to the other sheet
sh2.Cells(i, 1).PasteSpecial xlPasteAll

End Sub