Solved: Data Collection

[chem101]

Good Morning Everyone,

I've set up a pricing worksheet. Users enter information to and if the pricing is acceptable the user completes a second worksheet that is used to retrieve products in our warehouse. I would like to save the information that the user enters in a separate worksheet. I would like the user to enter all the pertinent info as they currently are doing and then save the information entered to another worksheet. Each field the user enters data in currently would be a column in this new worksheet. I would then be able to create reports from this information. This worksheet would look very much like an Excel table but I don't want to use Excel's Data Form. I want to continue using the data entry worksheets I currently have in place. The code I've tried to accomplish this task working for me. Can you help? Here's the code I have so far:

Sub GetData()
Dim NextRow As Long
Dim Entry1 As String, Entry2 As String

'Determine next empty row
NextRow = Cells(Rows.Count, 1).End(xlUp).Row + 1

Entry1 = wksTestSht.Range("C10")
If Entry1 = "" Then Exit Sub

Entry2 = wksTestSht.Range("C11")
If Entry2 = "" Then Exit Sub

'Write the data
Cells(NextRow, 1) = Entry1
Cells(NextRow, 2) = Entry2

End Sub

Any help you can provide will, as always, be greatly appreciated!!

[Aussiebear]

Should the Entry1 & Entry2 be values rather than locations?

[chem101]

I don't think so. I want the user to be able to enter data into the cell locations and then be able to save and clear the data entry form while the data they entered is saved in another worksheet in a table-like format.

[Tinbendr]

This appears to work.

Sub GetData()
Dim NextRow As Long
Dim wsSource As Worksheet
Dim wsDest As Worksheet

Set wsSource = Worksheets("MySource")
Set wsDest = Worksheets("MyDest")
'Determine next empty row
NextRow = wsDest.Cells(Rows.Count, 1).End(xlUp).Row + 1

If wsSource.Range("C10").Value <> "" Then
wsDest.Cells(NextRow, 1) = wsSource.Range("C10").Value
wsSource.Range("C10").ClearContents
If wsSource.Range("C11").Value <> "" Then
wsDest.Cells(NextRow, 2) = wsSource.Range("C11").Value
wsSource.Range("C11").ClearContents
End If
End If

End Sub
David

[chem101]

David,
This works great!
Thank you very much for your time, effort and generosity!

[chem101]

David,
I used .Copy instead of requesting a value (""). This seems to work.
Thanks again for your help.