master sheet to work sheet, Solved/Closed

Question

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

vcoolio · Answer

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.

vcoolio · Answer

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.

vcoolio · Answer

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.

vcoolio · Answer

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

vcoolio · Answer

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.

vcoolio · Answer

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.

vcoolio · Answer

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.

vcoolio · Answer

Hello Vicki,

You're welcome. Glad that I could help.

Take care.

Cheerio,
vcoolio.

vcoolio · Answer

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.

vcoolio · Answer

Hello Vicky,

Try the following code in place of the "TransferData" macro (Module 1):-


Sub TransferDataStuff()Application.ScreenUpdating = FalseDim lRow As LongDim MySelection As StringlRow = Range("A" & Rows.Count).End(xlUp).RowMySelection = InputBox("Please select the sheet you wish to enter the data into.")If MySelection = vbNullString Then Exit SubSheets("Master").SelectRange("A6:J6").CopySheets(MySelection).Range("A6:J6").PasteSpecial xlPasteValuesRange("A7:J7").CopySheets(MySelection).Range("A7:J7").PasteSpecial xlPasteValuesRange("A9:J9").CopySheets(MySelection).Range("A9:J9").PasteSpecial xlPasteValuesRange("A10:J10").CopySheets(MySelection).Range("A10:J10").PasteSpecial xlPasteValuesRange("A11:J11").CopySheets(MySelection).Range("A11:J11").PasteSpecial xlPasteValuesRange("A12:J12").CopySheets(MySelection).Range("A12:J12").PasteSpecial xlPasteValuesRange("A13:J13").CopySheets(MySelection).Range("A13:J13").PasteSpecial xlPasteValuesRange("A14:J14").CopySheets(MySelection).Range("A14:J14").PasteSpecial xlPasteValuesRange("A15:J15").CopySheets(MySelection).Range("A15:J15").PasteSpecial xlPasteValuesRange("A16:J16").CopySheets(MySelection).Range("A16:J16").PasteSpecial xlPasteValues          Sheets(MySelection).SelectApplication.ScreenUpdating = TrueApplication.CutCopyMode = FalseEnd 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.

vcoolio · Answer

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.

vcoolio · Answer

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.

Master sheet to work sheet,

12 responses

Subscribe To Our Newsletter!