Moving Data from one Sheet to another

[Simbo]

Moving Data from one Sheet to another using a Form

This is all new to me and all i am looking for is a starting point that i can work off of.

I have 2 sheets 1 called (MAIN) and 1 called (NAMES)

In Sheet Names, Column A will have Persons First name, Column B last name, Column C Phone Number,

What i would like to do is....


Have a Form that i can call up that has a ComboBox, for looking up first Names in sheet (NAMES) and 2 text boxes 1 that will show last name and 1 that will show phone #, when i click on the first name that i am looking for in the combo box, ,or just have the Combobox, click on the name i am looking for and have it send all the info for that name (1st, last, and # to the (MAIN) sheet

I allready know how to make the forms, i just need the code for pulling up the data off the NAME sheet in the combo box and have it fill in the other text boxes.

Or if you could atleast tell me how to get a combo box to show the data from the (NAMES) sheet i might be able to work out the rest on my own.

Any help would be great

[jindon]

paste the code onto form module

Please Login or Register  to view this content.

[Simbo]

Thanks Jindon

That helps, i am all most there but i get a Run-time error after the data is sent to the page, says

Could Not set the value property. type Mismatch.

I hit debug and it goes to this line in the code

Me.txtOurCost = Application.VLookup(Me.SalesList, .Range("a:d"), 2, False)


Here is the code i am using in that Form

Option Explicit

Private Sub cmdAdd_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Table")

'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row


'copy the data to the database
ws.Cells(iRow, 1).Value = Me.txtDate.Value
ws.Cells(iRow, 2).Value = Me.SalesList.Value
ws.Cells(iRow, 3).Value = Me.txtOurCost.Value
ws.Cells(iRow, 4).Value = Me.txtRetailPrice.Value
ws.Cells(iRow, 5).Value = Me.txtAmountSold.Value

'clear the data
Me.txtDate.Value = ""
Me.SalesList.Value = ""
Me.txtOurCost.Value = ""
Me.txtRetailPrice.Value = ""
Me.txtAmountSold.Value = ""
Me.txtDate.SetFocus
End Sub

Private Sub cmdClose_Click()
Unload Me
End Sub



Private Sub SalesList_Change()
With Sheets("inventory")
Me.txtOurCost = Application.VLookup(Me.SalesList, .Range("a:d"), 2, False)
Me.txtRetailPrice = Application.VLookup(Me.SalesList, .Range("a:d"), 3, False)
End With
End Sub

Private Sub UserForm_Initialize()
Dim i As Long, r As Range
With Sheets("Inventory")
For Each r In .Range("a2", .Range("a65536").End(xlUp))
Me.SalesList.AddItem r.Value
Next
End With
End Sub


Private Sub UserForm_QueryClose(Cancel As Integer, _
CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
Cancel = True
MsgBox "Please use the button!"
End If
End Sub



Any help would be Great