Master sheet to work sheets [Closed]

Report
Posts
27
Registration date
Tuesday June 9, 2015
Status
Member
Last seen
September 3, 2015
-
Posts
1292
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
May 7, 2021
-
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

13 replies

Posts
1292
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
May 7, 2021
229
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.
Posts
27
Registration date
Tuesday June 9, 2015
Status
Member
Last seen
September 3, 2015

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
Posts
1292
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
May 7, 2021
229
Hello Vicki,

The link isn't working. Could you try again please.

Cheerio,
vcoolio.
Posts
27
Registration date
Tuesday June 9, 2015
Status
Member
Last seen
September 3, 2015

Posts
1292
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
May 7, 2021
229
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.
Posts
27
Registration date
Tuesday June 9, 2015
Status
Member
Last seen
September 3, 2015

thank you so much for taking the time to help me with this. The more I learn about Excel, the more things I see that it can do, very exciting. I didn't know about modules but I think that I figured them out. I will work on it this weekend to see how I make out translating it to my worksheet.
Again thanks, I will comment and let you know how it worked.
Wayvic
Posts
1292
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
May 7, 2021
229
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.
Posts
27
Registration date
Tuesday June 9, 2015
Status
Member
Last seen
September 3, 2015

thanks again, I did not have a chance to play with it this weekend but will be today. I have another question. I was wondering if I am able to make the master a complete input medium IE: name, course name, hire date etc. All the areas that you see on my master to be able to input the information to be sent to a specific sheet. I do so appreciate you helping me with this, it is a great learning experience as well.

Wayvic
Posts
1292
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
May 7, 2021
229
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.
Posts
27
Registration date
Tuesday June 9, 2015
Status
Member
Last seen
September 3, 2015

Yes, thanks that is the way I do want to go. It sure would make things simpler. I did find an issue with my other worksheets, for some reason, are not in a row when I check on view code. That is easily fixed by making a new worksheet. No issues there. I did figure out the modules end of it.
Thanks for all you do.
Wayvic
Posts
27
Registration date
Tuesday June 9, 2015
Status
Member
Last seen
September 3, 2015

Another issue has cropped up. I have added the code to my play workbook, I finally have it that the double click box comes up but when I put in the worksheet that I want it to go to I get Runtime error'9': subscript out of range. an it brings me to the last page of the workbook but adds nothing.
???
Wayvic
Posts
1292
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
May 7, 2021
229
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.
Posts
1292
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
May 7, 2021
229
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.
Posts
27
Registration date
Tuesday June 9, 2015
Status
Member
Last seen
September 3, 2015

Thank you so much, it works really well. I have one more question for you. 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. The columns have the name of the competency and the rows have the name of the staff. I want to be able to dump the date from the individual work sheet to this table. Sort of a pivot table I guess you would call it. Is this possible.
Thanks.
Wayvic
Posts
1292
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
May 7, 2021
229
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.
Posts
27
Registration date
Tuesday June 9, 2015
Status
Member
Last seen
September 3, 2015

It is a separate worksheet or can be a separate work book. Thanks so much for looking at this further for me.

https://www.dropbox.com/s/lszk3z43i6g0yy8/New%20copy%20of%20education%20template%202015.xlsm?dl=0

Vicki
Posts
1292
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
May 7, 2021
229
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.
Posts
27
Registration date
Tuesday June 9, 2015
Status
Member
Last seen
September 3, 2015

There are actually 2 workbooks. One for CNA's and one for licensed. In each workbook there are the worksheets (like the one that you fixed for me) there will also be a work sheet with a table with columns B-H (more for the licensed) will be the names of the required yearly education and down column A will be all the names of the staff. Under columns B-H starting with the first name in column A will be the date the course was completed. The reason for the individual worksheets is that other education is done during the year other than required and it needs to be captured. I figured that I can get the licensed workbook up once I got the code, just have to do a little adjustment. If I can get my work laptop up and running I will send you a sample. Someone else used the laptop today and it is not working like it should.
Posts
27
Registration date
Tuesday June 9, 2015
Status
Member
Last seen
September 3, 2015

I finally have this done. Here is what I was talking about. I don't think that I downloaded the nice master that you created, hope this is useful .

https://www.dropbox.com/s/90tmooivnow6niq/education.xlsm?dl=0

Thanks, Vicki
Posts
1292
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
May 7, 2021
229
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.
Posts
27
Registration date
Tuesday June 9, 2015
Status
Member
Last seen
September 3, 2015

I have 2 workbooks. One is for CNA education, the other is for Licensed education. Both workbooks are to have a master, a worksheet of required education and numerous individual worksheets ( can be up to 100 in the CNA workbook, depending one the facility) The required education is yearly and unchanged, so that will be unchanged on the required education worksheet. There will also be some random educations that are taken (mostly the CNA's) that just need to be captured on the individual worksheets. CNA's only have about 6 to 7 required yearly required competencies. I believe I only have one or more things to add to this sheet and it will be complete. The Licensed staff have about 20 required yearly competencies, this will not change. They also have to occasional random education that needs to be captured only on the individual sheet You do have the flow correct.
So one workbook will actually be named CNA Education.xlsm and the other workbook will be Licensed Education.xlsm
Thanks
Vicki
Posts
1292
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
May 7, 2021
229
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.

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!