VBA Code Enter Data to Selected Sheet and Target Cells

HansVogelaar 

Awesome, that worked 🙂 I appreciate it 

I am wondering if there is a way to make this idea a reality or I may be over thinking, My UserForm is now able to complete data into those cells using the code, however, is there a way for pre-existing data to show as soon as they select the Active Sheet in the drop down.

Currently the code for the dropdown menu and the data submission looks like this.

Private Sub CommandButton1_Click()
TargetSheet = Selection.Value
If TargetSheet = "" Then
Exit Sub
End If
Worksheets(TargetSheet).Activate
ActiveSheet.Cells(3, 4).Value = FN.Value
ActiveSheet.Cells(3, 6).Value = SN.Value
ActiveSheet.Cells(4, 4).Value = PN.Value
ActiveSheet.Cells(5, 4).Value = MN.Value
ActiveSheet.Cells(5, 6).Value = WN.Value
ActiveSheet.Cells(4, 6).Value = Minder.Value
ActiveSheet.Cells(8, 4).Value = A1.Value
ActiveSheet.Cells(8, 6).Value = PR1.Value
ActiveSheet.Cells(9, 4).Value = A2.Value
ActiveSheet.Cells(9, 6).Value = PR2.Value
ActiveSheet.Cells(10, 4).Value = A3.Value
ActiveSheet.Cells(10, 6).Value = PR3.Value
ActiveSheet.Cells(11, 4).Value = SAD.Value
ActiveSheet.Cells(11, 6).Value = FR.Value
ActiveSheet.Cells(14, 4).Value = Start.Value
ActiveSheet.Cells(14, 6).Value = Fit_Note_Expiry.Value
ActiveSheet.Cells(15, 4).Value = Last_Call.Value
ActiveSheet.Cells(15, 6).Value = Next_Call_Due.Value
ActiveSheet.Cells(16, 4).Value = SSM_Meeting_Date.Value
ActiveSheet.Cells(16, 6).Value = Current_MFA.Value
ActiveSheet.Cells(17, 4).Value = Return_Date.Value
ActiveSheet.Cells(17, 6).Value = TNA_Completed.Value
MsgBox ("Data Submitted")

FN.Value = ""
SN.Value = ""
PN.Value = ""
MN.Value = ""
WN.Value = ""
Minder.Value = ""
A1.Value = ""
PR1.Value = ""
A2.Value = ""
PR2.Value = ""
A3.Value = ""
PR3.Value = ""
SAD.Value = ""
FR.Value = ""
Start.Value = ""
Fit_Note_Expiry.Value = ""
Last_Call.Value = ""
Next_Call_Due.Value = ""
SSM_Meeting_Date.Value = ""
Current_MFA.Value = ""
Return_Date.Value = ""
TNA_Completed.Value = ""
Worksheets("Main").Activate
Worksheets("Main").Cells(1, 1).Select
End Sub

Private Sub CommandButton2_Click()
CMS.Hide
MsgBox ("Exiting CMS")

End Sub


Private Sub UserForm_Initialize()

With Selection
[Redacted since the Sheet list is Surnames]
End With

With Minder
.AddItem "Yes"
.AddItem "No"
End With

End Sub

Again, any help would be appreciated