VBA - Data Entry Sheet

[Smilechild793]

I have a workbook with 4 sheets.

The "Input Sheet" has data made to resemble a Userform (Cells B4,B6,&B8 have descriptions and cells C4,C6,&C8 have bank cells for data input). Sheet1, Sheet2, & Sheet3 are all the same with A1,B1,&C1 containing the headers laid out in the Input Sheet.

Basically, I am attempting to make a Userform-like sheet where my employees can input the necessary data into cells C4,C6,&C8. Then select a button below those cells that will transfer it to whatever sheet they choose in the whichsheet inputbox that appears.

I have everything working except for the transfer part. I do not know how to reference the cells I want to move (C4,C6,C8) and connect that to the decision made for the whichsheet portion.

Ex: if someone inputs their data then pushes the button. Then selects that they want it moved to Sheet2. How do I get that specific data to move to the correct sheet they choose?

Please see code below (I bolded where I think the problem is occurring):

[FONT=Arial]Private Sub CommandButton2_Click()[/FONT][FONT=Arial]whichsheet = InputBox("In which sheet do you wish to enter data?", "Sheet Number")[/FONT]
[FONT=Arial]If whichsheet = "" Then[/FONT]
[FONT=Arial]MsgBox "You didn't specify a sheet!"[/FONT]
[FONT=Arial]Exit Sub[/FONT]
[FONT=Arial]End If[/FONT]
[FONT=Arial]
[/FONT]
[FONT=Arial]Worksheets(whichsheet).<wbr>Activate[/FONT]
[FONT=Arial]Dim lastrow[/FONT]
[FONT=Arial]lastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row[/FONT]
[FONT=Arial]
[/FONT]
[FONT=Arial]lastrow = lastrow + 1[/FONT]
[FONT=Arial]Cells(lastrow, 1) = Application.Workbooks("TEST").<wbr>Worksheets("Input").Cells(4, "C")[/FONT]
[FONT=Arial]
[/FONT]
[FONT=Arial]If Application.WorksheetFunction.<wbr>CountIf(Range("A2:A" & lastrow), Cells(lastrow, 1)) > 1 Then[/FONT]
[FONT=Arial]MsgBox "Duplicate data! Only unique IDs allowed", vbCritical, "Remove Data"[/FONT]
[FONT=Arial]Cells(lastrow, 1) = ""[/FONT]
[FONT=Arial]ElseIf Application.WorksheetFunction.<wbr>CountIf(Range("A2:A" & lastrow), Cells(lastrow, 1)) = 1 Then[/FONT]
[FONT=Arial]answer = MsgBox("Are you sure you wan to add the record?", vbYesNo + vbQuestion, "Add Record")[/FONT]
[FONT=Arial]If answer = vbYes Then[/FONT]
[FONT=Arial][B]Cells(lastrow, 1) = .Cells(4, 3)[/B][/FONT]
[FONT=Arial][B]Cells(lastrow, 2) = .Cells(6, 3)[/B][/FONT]
[FONT=Arial][B]Cells(lastrow, 3) = .Cells(8, 3)[/B][/FONT]
[FONT=Arial]
[/FONT]
[FONT=Arial]End If[/FONT]
[FONT=Arial]End If[/FONT]
[FONT=Arial]End Sub[/FONT]

 

[My Aswer Is This]

Glad you have what you want. Take care.