|re: "Setting this up as a Module, I'm getting an error "Expect End Sub""|
I have no idea what you mean by "Setting this up as a Module".
VBA code is stored in a Module. Event driven macros, such as a Worksheet_Change macro, must be stored in the Module for the Sheet in which the event will occur. In this case, the code must be stored in the Sheet1 Module so that it can monitor changes made to Sheet1 .
If you are getting an Expected End Sub error, then you must not have included an End Sub instruction at the end of the macro. Perhaps you didn't copy everything that I posted in my previous response?
This code should start pasting your data in Row 5 and increment 1 Row for each Paste operation.
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("G5:G5000")) Is Nothing Then
If Target = "OK" Then
nxtRw = Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row + 1
If nxtRw < 5 Then nxtRw = 5
Range("A" & Target.Row & ":F" & Target.Row).Copy
Sheets("Sheet2").Range("A" & nxtRw).PasteSpecial Paste:=xlPasteValues
Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.