Master sheet to work sheet, [Solved/Closed]

Report
Posts
27
Registration date
Tuesday June 9, 2015
Status
Member
Last seen
September 3, 2015
-
Posts
1260
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 3, 2020
-
I have been getting help at this site and I am not able to get into my messages to respond anymore. I am not sure if it is just a work computer issue but I have been corresponding on my work computer most of the time. I am still in the process of solving my inquire. Can you help please. The person that I have been corresponding with is vcoolio. This is a great service and kudos to everyone here that helps out.

Wayvic

12 replies

Posts
1260
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 3, 2020
213
Hello Vicki,

Try your previous link in your own PC:-

https://ccm.net/forum/affich-819112-master-sheet-to-work-sheets

There are some more questions for you!

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

I will do that when I get home this afternoon, Thank you so very much. Have a great day.
Posts
27
Registration date
Tuesday June 9, 2015
Status
Member
Last seen
September 3, 2015

I have sent the workbook that you needed to look at to my home email. I will open up the previous link from there and send it out to you this afternoon.
thanks
Vicki
Posts
1260
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 3, 2020
213 >
Posts
27
Registration date
Tuesday June 9, 2015
Status
Member
Last seen
September 3, 2015

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

Again my work computer will not let me into the other conversation and I did not have my laptop at home. I did look at the program this am and it is very close. Love how it transfer. It is not transferring into the correct place on the required education worksheet. I am sending you exactly what I need. I so very much appreciate all the work you have done getting this up and running.
https://www.dropbox.com/s/jy41f8r3kzmunb1/Copy%20of%20Wayvic%28CNA%20Education%206-29-15.xlsm?dl=0
Vicki
Posts
1260
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 3, 2020
213
Hello Vicki,

I'm not sure what you mean:-

"It is not transferring into the correct place on the required education worksheet."


I've had a good close look at it for you and all is working as it should. The only discrepancy that I picked up on in the work book link you supplied is that in Sheet 2 you have "Dementia", with the required date, in Row 9 whereas in Sheet 1 it is in Row 10 where it should be. So, if it is in Row 10, it will go to the correct position in the Required Education sheet. The code is based on what you have said in previous posts that the list of competencies will remain static in the same order in the Master sheet and the individual sheets as they will be assessed in that fixed order and then transferred to the individual sheet in that order after which the dates of the competencies will be transferred to the Required Education sheet. The competencies in the Required Education sheet sample that you supplied are in fixed order also (only horizontally) to match the individual sheets.
Based on what you have said about the number of individual sheets becoming quite large, you will need an "order of things" in your entries in order to keep the code as simple and efficient as possible.
So, basically, the Master sheet determines the "order of things" for all the other sheets otherwise it could all become quite messy and complicated.

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

Agreed, I did try to put things in the master then adding to sheet 4, which works great. The transfer data to the required education only worked on the first 2 columns and then added to the first row. Not sure if it can be specific to the name in the worksheet IE: Static names in Column A with the transfer to required dates going to the right column and name.

https://www.dropbox.com/s/jy41f8r3kzmunb1/Copy%20of%20Wayvic%28CNA%20Education%206-29-15.xlsm?dl=0

Thanks
Vicki
Posts
1260
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 3, 2020
213
Hello Vicki,

I'm not following.

 "The transfer data to the required education only worked on the first 2 columns and then added to the first row."


Based on Sheet 4 as an example, the sheet name is correctly going to Column A and the three dates you have in Column I are going to the correct cells in the Required Education sheet. Which two columns are you referring to and in which sheet?

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

I was playing with it to see if I was doing something wrong but I will try to explain. I want the names in the required education to remain static in column A and that the dates from the individual work sheet goes to the correct competency and that when the dates change that they will be overwritten by the new date. I did play with it and when dates were input in the individual sheet ( this works beautifully) and I use the send to required education, the date goes into the first 2 required education columns but not the others. I will have the required education in each individual education sheet the exact same as in the master and the master will be in the order (vertically) to the worksheet (horizontally). I hope that I am explaining myself better.
Thanks you so very much.
Vicki
Posts
1260
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 3, 2020
213
Hello Vicki,

It may be best to use formulae rather than VBA for this aspect. I'll get back to you.

Cheerio,
vcoolio.
Posts
1260
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 3, 2020
213
Hello Vicki,

Before we go down the formula road, try the following amended code (Module 3):-

Sub GatherDates()

Application.ScreenUpdating = False

     Dim ws As Worksheet
     Dim ws1 As Worksheet
     Dim ShtName As String
     Set ws = ActiveSheet
     Set ws1 = Sheets("Required Education")
     ShtName = ws.Name

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

ws1.Select

For Each cell In Range("A3:A500")
         If cell = ShtName Then
         cell.EntireRow.Delete
         End If
Next cell

ws.Select

If ws.Name = ShtName Then

 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
    End If
    
ws1.Range("A" & nextrow) = ws.Name

On Error Resume Next
ws1.Columns("A").SpecialCells(4).EntireRow.Delete
Application.ScreenUpdating = True
Application.CutCopyMode = False
ws1.Select

End Sub


With this code, it won't be necessary to have a static list of names in Column A of the Required Education sheet (let the code do this task for you, especially if you intend to have many more sheets). You'll be able to randomly work on any sheet and transfer details randomly. It takes the name from each individual sheet on transferring the dates to each competency column and when you update the dates from the individual sheets, the new dates will over-write the old ones in each column beside the individual name whether it be one date or the whole lot.

Following is the link to the updated test work book:-

https://www.dropbox.com/s/jqr2s33a3tck2mm/Wayvic%28CNA%20Education%29-2.xlsm?dl=0

Transfer the dates that I have placed in each individual sheet by clicking on the "Send to Required Education" button and once all these have been transferred to the Required Education sheet, go back to the individual sheets and randomly change the dates and see them update in the Required Education sheet.

See what you think of it.

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

Sorry I haven't responded. I am on vacation and have my 2 grandsons. I will be opening and looking at this when I get back in the office on Monday July 13th. Thanks again for all your help. I will get back to you as soon as I can on Monday.
C'ya
Vicki
Posts
1260
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 3, 2020
213
Hello Vicki,

I hope that you are having a great holiday.

Just in relation to the code above (post #11), I have finally trimmed the code down somewhat for you which I meant to do about 3000 posts ago! It does the same job as previous but should be a little more efficient now. Here it is (with the DropBox link following it):-

Sub GatherDates()

Application.ScreenUpdating = False

     Dim ws As Worksheet
     Dim ws1 As Worksheet
     Dim ShtName As String
     Set ws = ActiveSheet
     Set ws1 = Sheets("Required Education")
     ShtName = ws.Name

nRow = ws1.Cells(Rows.Count, 2).End(xlUp).Row + 1

ws1.Select

For Each cell In Range("A3:A500")
         If cell = ShtName Then
         cell.EntireRow.Delete
         End If
Next cell

ws.Select

If ws.Name = ShtName Then
    Range("I9:I16").Copy
    ws1.Range("B" & nRow).PasteSpecial xlPasteValues, Transpose:=True
End If
    
ws1.Range("A" & nRow) = ws.Name

On Error Resume Next
ws1.Columns("A").SpecialCells(4).EntireRow.Delete
Application.ScreenUpdating = True
Application.CutCopyMode = False
ws1.Select

End Sub


https://www.dropbox.com/s/jqr2s33a3tck2mm/Wayvic%28CNA%20Education%29-2.xlsm?dl=0

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

Hi ,
Sorry I did not get back to you sooner, work was crazy when I got back. The program is working nicely. The only question I have is it possible to have the names on the worksheet stay in the same order. I notice when I add a date or change a date the name goes to the bottom of the list. Everything goes under the right column but the name moves to the bottom. The only problem I have with that is some centers will have about 100 staff and keeping them in alphabetical order is important. Thanks for all you have done for me.
Have a great day, hope you are not having the heat wave, we are too cold here for being summer time.
Vicki
Posts
1260
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 3, 2020
213
Hello Vicki,

No, no heat wave here at the moment. Its winter and the last week or so has really been "brass monkey" weather!

I'm assuming that all the names in the Required Education sheet will be alphabetical with surnames first so I've just added a sort button for you on the sheet with the following code assigned to it:-



Sub Sort()

Sheets("Required Education").Range("A2:I500").Sort key1:=Range("A3:A500"), order1:=xlAscending, Header:=xlYes

End Sub


Here's the updated link:-

https://www.dropbox.com/s/jqr2s33a3tck2mm/Wayvic%28CNA%20Education%29-2.xlsm?dl=0

I did it this way just to keep things simple.

Anyway, I hope that you had a great holiday and that you're now on the "straight and narrow" with this particular project.

Good luck!

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

Thank you, Thank you, Thank you. It works great. You are amazing. Have a wonderful day.

Vicki
Posts
1260
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 3, 2020
213
Hello Vicki,

You're welcome. Glad that I could help.

Take care.

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

Hi
It's me again. I haven't been able to really work with the worksheet until this week. The big problem I see is that If I have dates in an individual record and on the flow sheet. when I put in new dates on the Master once the master dates have been cleared and transfer this to an individual sheet and the worksheet, the previous dates are erased so I loose these dates. I am constantly putting dates on different competencies as they are not done at the same time. So I may update one competency but clear out the other dates from the other ones. Any way to fix this? On my copy I set the master up correctly and have clear master to clear only the dates and hours, which works well.
thanks
Posts
1260
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 3, 2020
213
Hello again Vicky,

I'm just trying to understand what you mean (I'm referencing my test work book BTW):-

- From the Master sheet, you transfer dates to an individual sheet (say Bob) once his competencies are done. These dates are listed in order of the competencies completed in Bob's sheet. You then clear the Master sheet ready for the next set of competencies to be completed by another individual.

- From Bob's individual sheet, you then transfer the dates to the Required Education sheet where these dates are listed under the relevant competency. You can manipulate/change these dates in Bob's sheet if required and then transfer the manipulated dates to the Required Education sheet. Not all dates are necessarily altered.

- If you transfer more dates from the Master sheet to Bob's sheet, all the older dates, altered or not, are overwritten by the new dates.

I think I've summarised it!

However, are not the older dates still recorded in the Required Education sheet and only updated at your discretion from Bob's sheet (or any other individual sheet) as needed? That is, you can update one, a few or the lot at your discretion?

Let me know whether or not I'm on track.

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

I believe that you understand. I only really got to work with it the other day, I put in some competency dates in an individual (say individual A) record from the master (which worked beautifully) then to the worksheet . I cleared the dates from the master and put in dates in another individuals (individual B) sheet and flow sheet. I cleared the master again and put in another date on a competency that I had forgotten for individual A. When I transferred this to the individual sheet, it cleared all the other dates that were there on the other competencies ( because obviously I did not put the dates on the competencies that I had already dated) and (just to see what would happen) I transferred this to the worksheet and it only transferred the last date, and cleared out the other dates.

Thanks
Vicki
Posts
1260
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 3, 2020
213
Hello Vicky,

Try the following code in place of the "TransferData" macro (Module 1):-


Sub TransferDataStuff()

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:J6").Copy
Sheets(MySelection).Range("A6:J6").PasteSpecial xlPasteValues
Range("A7:J7").Copy
Sheets(MySelection).Range("A7:J7").PasteSpecial xlPasteValues
Range("A9:J9").Copy
Sheets(MySelection).Range("A9:J9").PasteSpecial xlPasteValues
Range("A10:J10").Copy
Sheets(MySelection).Range("A10:J10").PasteSpecial xlPasteValues
Range("A11:J11").Copy
Sheets(MySelection).Range("A11:J11").PasteSpecial xlPasteValues
Range("A12:J12").Copy
Sheets(MySelection).Range("A12:J12").PasteSpecial xlPasteValues
Range("A13:J13").Copy
Sheets(MySelection).Range("A13:J13").PasteSpecial xlPasteValues
Range("A14:J14").Copy
Sheets(MySelection).Range("A14:J14").PasteSpecial xlPasteValues
Range("A15:J15").Copy
Sheets(MySelection).Range("A15:J15").PasteSpecial xlPasteValues
Range("A16:J16").Copy
Sheets(MySelection).Range("A16:J16").PasteSpecial xlPasteValues



Sheets(MySelection).Select

Application.ScreenUpdating = True
Application.CutCopyMode = False

End Sub


I just now slapped it together and its a bit rugged but I just want to see if it clears up the issues from your last post in the meantime. I'll have another look at it for you when I get back from working away (my real life!) within the next few days.

In the meantime, please post back and let me know how it goes.

Please use it in a copy of your work book only at this stage.

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

Thank you again, I will try and see how it works. Your help is much appreciated. Have fun working. !!
Vicki
Posts
27
Registration date
Tuesday June 9, 2015
Status
Member
Last seen
September 3, 2015

I tried it but it is still doing the same thing .
Sorry
Vicki
Posts
1260
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 3, 2020
213
Hello Vicki,

I'm back!

I've come up with a work - around seeing that the eight competency headings need to remain static in all sheets.

With the following code, I'm assuming that once the dates have been transferred from the Master sheet to the individual sheet (and then transferred to the Required Education sheet), they need to remain in the individual sheet for you to alter/manipulate as and when you require from the Master sheet.

Sub BackDates()

Application.ScreenUpdating = False
 
      Dim ws As Worksheet

For Each ws In Worksheets
      If ws.Name <> "Master" Then
      If Range("I1") = ws.Name Then
      ws.Range("I6:I44").Copy
      Sheets("Master").Range("I6").PasteSpecial xlPasteValues
      Sheets("Master").Range("I2") = ws.Name
      End If
End If
Next

Application.ScreenUpdating = True
Application.CutCopyMode = False
Sheets("Master").Select
Sheets("Master").Range("I1") = "Retrieve Dates"
End Sub


Following is the link to my updated test work book:-

https://www.dropbox.com/s/9tnd9026nfa3gn7/Wayvic%28CNA%20Education%29-3.xlsm?dl=0

Basically, the code takes the dates that have been transferred to the required individual sheet, in the same order, back to the Master sheet ready for you to alter or add to as you need.

So, on the Master sheet, add your entries as required, transfer them to the required individual sheet (say the "Vicki" sheet for example) and then transfer them to the Required Education sheet. Clear the Master sheet.

Now assume, at say a later date, that you have made an error or left out a training competency for "Vicki". Go back to the Master sheet and in the red search box "Retrieve Dates", type in Vicki and click on GO. The dates that you previously entered for Vicki will appear back on the Master sheet in the same order as previous (even if you have left blanks from the previous transfer). Now alter/manipulate the dates as required and then transfer the updates back to the Vicki sheet (and on to the Required Education sheet). Clear the Master sheet.

The dates will stay in the individual sheets and only change as and when you need them to change by following the above process over and over and................

To confirm that you have retrieved the dates from the correct sheet, the sheet name will appear in cell I2 on the Master sheet.

Have a play with the test work book and let me know what you think.

Cheerio,
vcoolio.
Posts
1260
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 3, 2020
213
Sorry Vicki,

Slight change to the code:-


Sub BackDates()

Application.ScreenUpdating = False
 
      Dim ws As Worksheet

For Each ws In Worksheets
      If ws.Name <> "Master" Then
      If Range("I1") = ws.Name Then
      ws.Range("A6:J44").Copy
      Sheets("Master").Range("A6").PasteSpecial xlPasteValues
      Sheets("Master").Range("I2") = ws.Name
      End If
End If
Next

Application.ScreenUpdating = True
Application.CutCopyMode = False
Sheets("Master").Select
Sheets("Master").Range("I1") = "Retrieve Dates"
End Sub


https://www.dropbox.com/s/9tnd9026nfa3gn7/Wayvic%28CNA%20Education%29-3.xlsm?dl=0

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

Hi again,
For some reason I am not able to get the retrieve dates to work. I have tried on the last 2 worksheets that you sent me. I put the name in the box under retrieve codes and hit go and nothing happens.?
Maybe I am doing it wrong.
Have a good day.
Vicki
Posts
1260
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 3, 2020
213 >
Posts
27
Registration date
Tuesday June 9, 2015
Status
Member
Last seen
September 3, 2015

Hello Vicki,

You need to place the name in the red box (overwrite "Retrieve Dates"). The box below it just confirms the individual sheet name from where the data has just come from. The sheet name will appear here only when the dates have been retrieved.

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

Ahh, I was putting the name below, It does work. thanks so much. I will work with it later today when things quiet down here. I believe you have solved my issue. Thanks so much and have a very wonderful day.
Vicki
Posts
1260
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 3, 2020
213 >
Posts
27
Registration date
Tuesday June 9, 2015
Status
Member
Last seen
September 3, 2015

No worries Vicki.

Let me know how it goes.

Cheerio,
vcoolio.