I've been at this for entirely too long. As much as I want to do this myself, I have resorted to asking for help.
I have a User Form, a "Data" sheet(for quick reference and a Pivot Table), a template, and individually named worksheets.
Here's the process:
User Form gets data
The data is added to the next available row in the table on "Data"
A new worksheet is created and named the person's full name.
A template is copied over
Data from the User Form is then put into the corresponding cells on the newly named sheet.
I have a Sub that creates links in the table on the "Data" worksheet to the corresponding named worksheets.
ex. Bob Smith's information is entered into the User Form. A new line is created on the table in the "Data" worksheet with all of that information. A new worksheet is created and named "Bob Smith", and then the template is copied over to it. Bob's information is then entered into the appropriate cells on his sheet.
So far, so good.
I would like the rest of the cells in the table on the "Data" worksheet to to reference their corresponding cells in the newly created worksheet. Meaning, If there is a change on Bob's sheet, it will change accordingly on the "Data" sheet.
I've gone in many circles, but have not been able to pin down what I could do. Any help would be great!
Private Sub CommandButton1_Click()
Dim TargetRow As Integer
Dim FullName As String
Dim NewName As String
Dim Sp As String
Sp = " "
TargetRow = Sheets("Engine").Range("B3").Value + 1
FullName = Text_FirstName & Sp & Text_LastName
'NewName = Text_Name
'Avoiding double entries
If Application.WorksheetFunction.CountIf(Sheets("Data").Range("C8:C509"), FullName) > 0 Then
MsgBox FullName & " already exists in your list!", 0, "Nope!"
Exit Sub
End If
'Nobody wants to see how the sausage is made
Application.ScreenUpdating = False
'Unhide Template
Sheets("Template").Visible = True
'INPUT DATA INTO THE RUNNING LIST (Database is a dirty word :) )
'UF Employee Info
Sheets("Data").Range("Data_Start").Offset(TargetRow, 1).Value = FullName
Sheets("Data").Range("Data_Start").Offset(TargetRow, 2).Value = Text_Address
Sheets("Data").Range("Data_Start").Offset(TargetRow, 3).Value = Text_WrkPhone
Sheets("Data").Range("Data_Start").Offset(TargetRow, 4).Value = Text_PrsnlPhone
Sheets("Data").Range("Data_Start").Offset(TargetRow, 5).Value = Text_PERNR
'UF Program Info
Sheets("Data").Range("Data_Start").Offset(TargetRow, 6).Value = Combo_Role
Sheets("Data").Range("Data_Start").Offset(TargetRow, 7).Value = Combo_Occupational
Sheets("Data").Range("Data_Start").Offset(TargetRow, 8).Value = Text_ShiftDays
Sheets("Data").Range("Data_Start").Offset(TargetRow, 9).Value = Text_ShiftHours
Sheets("Data").Range("Data_Start").Offset(TargetRow, 10).Value = Text_StartDate
Sheets("Data").Range("Data_Start").Offset(TargetRow, 11).Value = Text_ProjectedEnd
'Copy Template
Sheets("Template").Select
Cells.Select
Selection.Copy
'Create new named sheet
Sheets.Add
ActiveSheet.name = FullName
'Paste template to new named sheet
ActiveSheet.Paste
Range("A1").Select
'Copy information into newly named sheet
ActiveSheet.Range("C3").Value = FullName
ActiveSheet.Range("C5").Value = Text_Address
ActiveSheet.Range("C6").Value = Text_WrkPhone
ActiveSheet.Range("C7").Value = Text_PrsnlPhone
ActiveSheet.Range("C10").Value = Text_PERNR
ActiveSheet.Range("C13").Value = Combo_Role
ActiveSheet.Range("C14").Value = Combo_Occupational
ActiveSheet.Range("C15").Value = Text_ShiftDays
ActiveSheet.Range("C16").Value = Text_ShiftHours
ActiveSheet.Range("C17").Value = Text_StartDate
ActiveSheet.Range("C18").Value = Text_ProjectedEnd
'Set default table style
ActiveWorkbook.DefaultTableStyle = "TableStyleLight15"
'Create named table for Notes
ActiveSheet.ListObjects.Add(xlSrcRange, Range("E2:F102"), , xlYes).name = Text_LastName
'Housekeeping for my own sanity (or moderate lack thereof)
ActiveWindow.DisplayGridlines = False
'Reset "Template" sheet
Sheets("Template").Select
Range("A1").Select
'Hide Template
Sheets("Template").Visible = False
'Return to "Data" sheet
Sheets("Data").Select
Range("C8").Select
'Name-Linking Magic
Call NameToSheetLinker
'They just want to eat the sausage
Application.ScreenUpdating = True
'Close the userform
Unload Data_UF
'Confirmation
MsgBox FullName & " was added to the database", 0, "Success!"
End Sub
Display More