Master sheet to work sheets

Wayvic Posts 27 Registration date Tuesday June 9, 2015 Status Member Last seen September 3, 2015 - Jun 9, 2015 at 10:28 AM
vcoolio Posts 1362 Registration date Thursday July 24, 2014 Status Moderator Last seen September 29, 2022 - Jun 26, 2015 at 03:41 AM
I have an education master template and many other exact duplicate worksheets in a workbook. Each of the other worksheets is differentiated only by the name of an employee. The master and other worksheets has specific education topics / or competencies on the same rows. I only need to add the date that each topic / competency was completed. I want to be able to auto-populate the date from the master to a specific worksheet and to be able to update that date when necessary. I will have approximately 50 or so worksheets in one work book. I know that I need to write a program but not sure how. Also when a program is written do I only add this to the code of the master or do I have to add to each sheet as well.

13 replies

vcoolio Posts 1362 Registration date Thursday July 24, 2014 Status Moderator Last seen September 29, 2022 252
Jun 10, 2015 at 07:49 AM
Hello Wayvic,

If I have understood you correctly, you want only the required date transferred to whichever sheet you select.

I'm not sure if you want the date cell addresses fixed, i.e. say A2 in the Master sheet and A2 in all the other sheets which would mean that you have only one "input" cell (A2) in the Master sheet and only one date cell (A2) in each of the other sheets. These, of course, would be overwritten once a new date is placed in A2 in the Master sheet.

However, for the sake of the exercise, the following code assumes a date column (A) in the Master sheet and in all the other sheets. This way, you can place as many dates as you wish in Column A of the Master sheet, transfer them and then keep them in all sheets.
(Perhaps you could clarify all the above for us). The code is as follows:-

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

Application.ScreenUpdating = False

Dim lRow As Long
Dim MySelection As String

lRow = Range("A" & Rows.Count).End(xlUp).Row
MySelection = InputBox("Please select the sheet you wish to enter the date into.")
If MySelection = vbNullString Then Exit Sub

For Each cell In Range("A2:A" & lRow)
     If cell = ActiveCell Then
     Sheets(MySelection).Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
     End If


Application.ScreenUpdating = True
Application.CutCopyMode = False

End Sub

The code is a "Double Click" event, so you just double click on the required date and an Input Box will pop up. In the Input Box, type in the sheet (name or number) of your choice and the date will be transferred to the relevant sheet (Column A in this case).

You can peruse my test work book here:-

to see how it works.

To implement the code, right click on the Master tab and select "view code". In the big white field that appears, just paste the above code.

Please note that entries into the Input Box are case sensitive, so ensure that your spelling, punctuation etc. is exactly as per the sheet tab.

I hope that this helps.