Master sheet to work sheets Closed

Question

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. 
thanks

vcoolio · Answer

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

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

Sheets(MySelection).Select

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:-

https://www.dropbox.com/s/kx7hhlrniqv8p5m/Wayvic.xlsm?dl=0

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.

Cheerio,
vcoolio.

Wayvic · Answer

Thanks, I thought that I already replied but I don't see it. My date column is column I (but this can be changed to A if that works better) Also I have a about 20-25 rows that I will need to input dates and change as necessary on each sheet. I tried to put the code in that you wrote, even changing around the starting row (which is 7 not 2) but could not get it to work. I will attach what I have and a sample worksheet.

https://www.dropbox.com/login?cont=https%3A%2F%2Fwww.dropbox.com%2Fhome%3Fpreview%3Dmaster%2Band%2Bworksheet.xlsx

Thanks 
Vicki Fuller RN

vcoolio · Answer

Hello Vicki,

The link isn't working. Could you try again please.

Cheerio,
vcoolio.

Wayvic · Answer

https://www.dropbox.com/s/5kpzv6ciwd1yv0y/master%20and%20worksheet.xlsx?dl=0

vcoolio · Answer

Hello Vicki, Thanks for that. The reason that you didn't have any luck with the code working is that your file is a .xlsx type file. You'll need to change it to a type .xlsm (macro enabled) file. I've done this for you in a new test work book here:- https://www.dropbox.com/s/153ry95rpns6zl3/Wayvic%282%29.xlsm?dl=0 As you can probably tell, I've made some amendments. They are:- - I have created a new format for your data set without merged cells. Merged cells create havoc with vba codes and this now makes things a lot simpler. - The amended code (as below) allows for Column I for the dates. - The amended code is based on a fixed range (I7:I44) to match your form. - The code assumes that the Column A data will always be static for all sheets (i.e., all work sheets will always match the Master work sheet format). - I have tried to cover most angles for you and have assumed:- You may want the same dates to go to a number of sheets all at once but then, at the same time, you may want different dates to go to other sheets. Hence, the "Clear Master Dates" button on the Master sheet. This will allow you to enter new dates in the same "session". You will want to clear dates in the Master sheet on completing all data transfers to each individual sheet. The next lot of date entries will overwrite the previous in any sheet based on your "update dates" comment in your first post. Now, for the codes:- - In the Master work sheet module (right click on the Master tab, select "view code") and then enter this code in the big white field:- Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) Application.ScreenUpdating = False Dim lRow As Long Dim MySelection As String lRow = Range("I" & Rows.Count).End(xlUp).Row MySelection = InputBox("Please select the sheet you wish to enter the date into.") If MySelection = vbNullString Then Exit Sub AddChar Sheets("Master").Select If ActiveCell.Column = 9 Then Range("I7:I44").Copy Sheets(MySelection).Range("I7").PasteSpecial xlPasteValues End If Sheets(MySelection).Select Application.ScreenUpdating = True Application.CutCopyMode = False End Sub In a standard module, enter this code:- Sub AddChar() Dim ws As Worksheet For Each ws In Worksheets If ws.Name = "Master" Then GoTo NextSheet ws.Select If Cells(7, 9) <> "" And Cells(8, 9) <> "" Then Cells(9, 9) = " " End If NextSheet: Next ws End Sub In another standard module, enter this code:- Sub ClearContents() Sheets("Master").Range("I7:I44").ClearContents End Sub For the last code above, create a button (as per the test work book) on the Master sheet and assign the code to it (after creating the button, right click on it and select "assign macro". A pop up will appear and, in the larger field, select "ClearContents" then OK. Done!). A few more things to note:- Enter all your required dates in the Master sheet first then double click on any date to activate the Input Box (you can take it from here!). In each sheet (except the Master sheet), select cell I6 and tap the space bar ONCEonly. Do this for cell J6 next to it also. Do this in all the sheets (except the Master sheet). This will prevent the dates from hopping over into the Grey area where they are not wanted. Please play around with the test work book first before entering any codes into your actual work book. I hope that this helps. Cheerio, vcoolio.

vcoolio · Answer

Hello Vicki,

Glad that I could help. I reckon that we'll have to run through some basics like creating a button and implementing codes in a standard module but have a play and let me know how it goes. Bed time now!

Cheerio,
vcoolio.

vcoolio · Answer

Hello Vicki,

I thought that you may want to go down that road!

We sure can. It will probably actually simplify things a little.
It will mean changing your form format (Master sheet and individual sheets) a little; nothing dramatic though. You basically  want to use the Master sheet as an Input sheet and transfer the details to the relevant sheet as you go meaning that the individual sheets will be blank (other than headings) waiting for input from the Master sheet, correct?

Anyway, have a play with the other first. This will give you a better idea of how you could refine the whole work book to better suit your needs. Let me know in due course.

Cheerio,
vcoolio.

vcoolio · Answer

Hello Vicki,

That error usually means that the code can't find the relevant sheet to interact with. It generally means that there is a spelling, punctuation or general grammar mistake. Make sure that everything is spelt and punctuated exactly the same in the code, the work sheet tabs and the inputs into the Input Box. This includes spaces also. So, if the sheet starts with a capital "S" in the code then it needs to be exactly the same on the tab and in the Input Box (and vice versa). It actually teaches us good habits!

Anyway, I'm working on a new set up for you so keep the above in mind when I send it through to you.

I'll be back.

Cheerio,
vcoolio.

vcoolio · Answer

Hello again Vicki,

Have a look here:-

https://www.dropbox.com/s/rg4ciftgso0fv39/Wayvic%283%29.xlsm?dl=0

I've slightly changed the form format and added the following codes:-

For the data transfer:-

Sub TransferData()

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 data into.")
If MySelection = vbNullString Then Exit Sub

Sheets("Master").Select

Range("A6:J44").Copy
     Sheets(MySelection).Range("A6").PasteSpecial xlPasteValues

Sheets(MySelection).Select

Application.ScreenUpdating = True
Application.CutCopyMode = False

End Sub

For clearing the Master sheet:-

Sub ClearIt()

Sheets("Master").Range("A6:J7").ClearContents
Sheets("Master").Range("A9:J44").ClearContents

End Sub

The codes are in two separate modules (hence the two buttons). I did it this way instead of having just the one code because I thought that you may want to use the same data for a number of sheets, transfer it to whichever individual sheet and then clear it ready for new data. This way you won't have to re-type it all over again and again if its the same data for a number of individual sheets.

The code will update whichever sheet that you choose with new data, overwriting the previous.

Have a play with this new set up and let me know what you think.

BTW, check the formulae at the bottom of each form to ensure that they include the cells that you require.

Cheerio,
vcoolio.

vcoolio · Answer

Hello Vicki,

Is this a separate sheet in the same work book? If so, can you upload the work book again including this additional sheet just so I'll better understand your additional request.

Thanks Vicky.

Cheerio,
vcoolio.

vcoolio · Answer

Hello Vicki,

No need for another work book. 

I am assuming that your last query refers to the "Flowsheet".

"I have one table for each discipline (licensed staff and CNA's ) that I have all the mandatory education with the dates that they did this education".

Does this mean that there should actually be two work sheets, "Licensed Staff" and "CNAs" or are these just combined in the "Flowsheet"?
Where do you want the dates placed?
Where is the Flowsheet data (Columns A:H) to come from?

As you can see, I'm a little confused about the flow of things here.

Let me know.

Cheerio,
vcoolio.

vcoolio · Answer

Good day Vicki,

Now, lets see. You have two work books. The one in this link from your last post:-

https://www.dropbox.com/s/90tmooivnow6niq/education.xlsm?dl=0 

is named "Education.xlsm" so is the very first one we worked on named "CNA.xlsm"?

(Let me know the actual name of each work book).

Does the "Education" work book have a number of individual sheets besides the Master and Required Edu?

Will the very first work book we worked on (CNA.xlsm?) also have a "Required Edu" sheet?

In the "Required Edu" sheet, the column headings are named after each competency (Columns B:H). Column A has the staff member's name. Beside each staff member's name, from Column B to Column H, there will be dates of when each competency was completed (so Columns B:H will be filled with just dates). These dates will come from each individual staff member's sheet. This sheet will, in effect, be a summary of each staff member's training.
Have I got the expected flow correct?
If I have got this correct, do you want the "Required Edu" sheet to just grow and grow with new entries or do you want the previous entries to be overwritten by the new ones as training is updated and completed?
(My preference would be to overwrite the previous).

We could, probably, keep all this in one work book if you like.

Let me know in due course.

Cheerio,
vcoolio.

vcoolio · Answer

Hello Vicki,

Well, I think this may be it then:-

https://www.dropbox.com/s/79nlek4pjaai0pu/Wayvic%28CNA%20Education%29.xlsm?dl=0

This is the CNA Education work book with the latest code to do as you asked (I believe!) with the "Required Education" worksheet:-

Sub GatherDates()

Application.ScreenUpdating = False

Dim ws As Worksheet
     Dim ws1 As Worksheet
     Set ws = ActiveSheet
     Set ws1 = Sheets("Required Education")

nextrow = ThisWorkbook.Sheets("Required Education").Cells(Rows.Count, 2).End(xlUp).Row + 1

ws.Select

Range("I9").Copy
    ws1.Range("B" & nextrow).PasteSpecial xlPasteValues
    Range("I10").Copy
    ws1.Range("C" & nextrow).PasteSpecial xlPasteValues
    Range("I11").Copy
    ws1.Range("D" & nextrow).PasteSpecial xlPasteValues
    Range("I12").Copy
    ws1.Range("E" & nextrow).PasteSpecial xlPasteValues
    Range("I13").Copy
    ws1.Range("F" & nextrow).PasteSpecial xlPasteValues
    Range("I14").Copy
    ws1.Range("G" & nextrow).PasteSpecial xlPasteValues
    Range("I15").Copy
    ws1.Range("H" & nextrow).PasteSpecial xlPasteValues
    Range("I16").Copy
    ws1.Range("I" & nextrow).PasteSpecial xlPasteValues
    
ws1.Range("A" & nextrow) = ws.Name
    
Application.ScreenUpdating = True
Application.CutCopyMode = False
ws1.Select

End Sub

I have randomly named some of the individual sheets and placed some dates in Column I to cover the eight competencies that you will be placing into each individual sheet via the Master sheet. Just click on the "Send to Required Education" button in each sheet to send the dates (and the name of the staff member) to the Required Education sheet.
As the Licensed Education work book is basically the same, you can just make a copy of the CNA Education work book and rename it. You can at any time expand the number of sheets or the number of competencies in the Required Education sheet to suit your needs. There will, of course, be some minor adjustments required to the code (for the Required Education sheet). To expand the number of individual sheets, just make a copy of the first individual sheet (not the Master) and rename them to suit. This way, all the formatting (including the button with code attached) etc. will be copied also and you won't have to labour long and  hard to create, format and code each sheet.

Let me know how it goes.

Cheerio,
vcoolio.

Master sheet to work sheets

13 responses

Similar discussions

Subscribe To Our Newsletter!