VBA Code Needed

[nhbartos]

Hi folks,

I have a table to track Personal, sick, vacation and half days taken for 25 to 150 students.
They are recorded by placing an "S", "P", "V", or an "H" in the cells for the corresponding date.
There is a different tab for each month.
School year July 1 2016 to June 30 2017.

I would like some code to pull the dates for all category entries, for each month, then place them in a table within each students reporting tab and sorted by date.
Below is a partial July 2016 table.
[TABLE="width: 1011"]
<colgroup><col><col span="31"></colgroup><tbody>[TR]
[TD]July[/TD]
[TD="colspan: 31"]Dates of Absence[/TD]
[/TR]
[TR]
[TD]Fri[/TD]
[TD]Sat[/TD]
[TD]Sun[/TD]
[TD]Mon[/TD]
[TD]Tue[/TD]
[TD]Wed[/TD]
[TD]Thu[/TD]
[TD]Fri[/TD]
[TD]Sat[/TD]
[TD]Sun[/TD]
[TD]Mon[/TD]
[TD]Tue[/TD]
[TD]Wed[/TD]
[TD]Thu[/TD]
[TD]Fri[/TD]
[TD]Sat[/TD]
[TD]Sun[/TD]
[TD]Mon[/TD]
[TD]Tue[/TD]
[TD]Wed[/TD]
[TD]Thu[/TD]
[TD]Fri[/TD]
[TD]Sat[/TD]
[TD]Sun[/TD]
[TD]Mon[/TD]
[TD]Tue[/TD]
[TD]Wed[/TD]
[TD]Thu[/TD]
[TD]Fri[/TD]
[TD]Sat[/TD]
[TD]Sun[/TD]
[/TR]
[TR]
[TD]Student Name[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD]8[/TD]
[TD]9[/TD]
[TD]10[/TD]
[TD]11[/TD]
[TD]12[/TD]
[TD]13[/TD]
[TD]14[/TD]
[TD]15[/TD]
[TD]16[/TD]
[TD]17[/TD]
[TD]18[/TD]
[TD]19[/TD]
[TD]20[/TD]
[TD]21[/TD]
[TD]22[/TD]
[TD]23[/TD]
[TD]24[/TD]
[TD]25[/TD]
[TD]26[/TD]
[TD]27[/TD]
[TD]28[/TD]
[TD]29[/TD]
[TD]30[/TD]
[TD]31[/TD]
[/TR]
[TR]
[TD]Student 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Student 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Student 3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Student 4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Student 5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Student 6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Student 7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Student 8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Student 9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Student 10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Student 11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Student 12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Student 13

Is anyone able to help with this?

Vince[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[MickG]

Does the "Medicaid" column in sheet "Base Info" have a "Yes/No" in it or something else ???

Are the dates per Month in the Medicaid Summary report sheet, suppose to reflect the Various "Leave" dates in all the month sheets for a particular student, but only if there Medicaid column says "Yes", Or something else ????

And If the Medicaid column says "No" to a particular student, then do you want that student name in "Summary" sheet with "0" dates or not entered at all ????

 

[nhbartos]

The Medicaid Column has "Yes". Blank When No.

Medicaid Summary:
Only Medicaid kids on the summary.
They are those with "Yes" in the Medicaid Column.

 

[MickG]

Try this (complete code):-
Make sure you have a sheet called:- "Medicaid"
https://app.box.com/s/56b6tpqebg8p8xmeypmxz17ph7kui9tb

 

[nhbartos]

Hi!
Thanks. Very nice. So, a little bug...

I tested the code:

I entered various absent codes in these dates for all medicaid students:

7/1/17
7/2/17
7/3/17
7/4/17
7/5/17
7/6/17
7/7/17

So, seven dates per medicaid student.

It looks like the medicaid tab is formatting properly, but the dates/moths are all wrong and there is not the right number of them.
Also...is it possible to insert a blank row between students? I can format the rest.

So, this is what the medicaid tab data looks like after entering the dates above:

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Medicaid Students[/TD]
[TD]Jul_2016[/TD]
[/TR]
[TR]
[TD]Student 1[/TD]
[TD]05/07/16[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]03/07/16[/TD]
[/TR]
[TR]
[TD]Student 2[/TD]
[TD]05/07/16[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]03/07/16[/TD]
[/TR]
[TR]
[TD]Student 3[/TD]
[TD]05/07/16[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]03/07/16[/TD]
[/TR]
[TR]
[TD]Student 4[/TD]
[TD]05/07/16[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]03/07/16[/TD]
[/TR]
[TR]
[TD]Student 5[/TD]
[TD]05/07/16[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]03/07/16[/TD]
[/TR]
</tbody>[/TABLE]

 

[MickG]

Try this:-

Change the ">2" to ">1" in the line below:-

For n = 0 To 10 Step 2 ' added items from dict Now 10 was 7
            cc = cc + 1
            oMax = Application.Max(oMax, Dic(K)(n + 1))
            For c = 1 To Dic(K)(n + 1)
               
               'Sheets("July_2016").Cells(c + 18, cc + 1) = Dic(K)(n)(c)
               Sheets(K).Cells(c + 19, cc).Value = Dic(K)(n)(c) 'NB:- Start Od Dates shown Here as C+18
               Sheets(K).Cells(c + 19, cc).NumberFormat = "dd/mm/yyyy"
         [B][COLOR=#FF0000]   If c > 1 Then[/COLOR][/B]
            y = y + 1
            Ray(y, 2) = Dic(K)(n)(c)
            End If
            oMax = Application.Max(oMax, c)
            Next c
        Next n

To set blank Row between each student , add a "1" to the line below ,Shown in Red:_
To sort the Dates add the line in Red below
NB:-These lines are at the end of the code

Next m
.Cells(1, m + 2).Value = Left(Sp(0), 3) & "_" & Sp(1)
  .Cells(nl, 1).Value = r(1, 1)
  .Cells(nl, m + 2).Resize(Dic(Mths(m))(1)).Value = Application.Transpose(Dic(Mths(m))(0))
  [B][COLOR=#FF0000].Cells(nl, m + 2).Resize(Dic(Mths(m))(1)).Sort .Cells(nl, m + 2), xlAscending[/COLOR][/B]
  .Cells(nl, m + 2).Resize(Dic(Mths(m))(1)).NumberFormat = "dd/mm/yyyy"
End If
Next m
.Columns.AutoFit
End With
[B][COLOR=#FF0000]nl = nl + oMax + 1[/COLOR][/B]
End Sub

 

[nhbartos]

Thank you!!
It worked Perfectly.

 

[nhbartos]

The Medicaid tab...

Is it possible to have the logo inserted on this tab also?

Have "Medicaid Students" in A11?

Have the months in row 13?
Student names in row 14?

 

[MickG]

Change the lines below in SuB "Medic":-

With Sheets("Medicaid")
   [B][COLOR=#ff0000].Range("A11").Value = "Medicaid Students"[/COLOR][/B]
For m = 0 To UBound(Mths)
If Dic.exists(Mths(m)) Then
  For Each sht In Worksheets
    If InStr(sht.Name, "_") > 0 Then
        nSp = Split(sht.Name, "_")
        If nSp(0) = MonthName(Mths(m)) Then
           Sp = Split(sht.Name, "_")
           Exit For
        End If
    End If
  Next sht
  oMax = Application.Max(oMax, Dic(Mths(m))(1))
  .Cells[B][COLOR=#ff0000](13, [/COLOR][/B]m + 2).Value = Left(Sp(0), 3) & "_" & Sp(1)
  .Cells(nl, 1).Value = r(1, 1)
  .Cells(nl, m + 2).Resize(Dic(Mths(m))(1)).Value = Application.Transpose(Dic(Mths(m))(0))
  .Cells(nl, m + 2).Resize(Dic(Mths(m))(1)).Sort .Cells(nl, m + 2), xlAscending
  .Cells(nl, m + 2).Resize(Dic(Mths(m))(1)).NumberFormat = "dd/mm/yyyy"
End If
Next m

Change the lines below at top of Sub "StuUpdate" :-

Dim Dic As Object, Dn As Range, NumLog As Long
[B][COLOR=#ff0000]NumLog = 14[/COLOR][/B]
[B][COLOR=#ff0000]Sheets("Medicaid").Cells.ClearContents[/COLOR][/B]
[B][COLOR=#ff0000]Call s Pic(Sheets("Medicaid")) '
[/COLOR]'NB:- Remove the space in the Sub Name !!!!

[/B]

 

[nhbartos]

Thanks so much!!!

I am going to continue tweaking as I need to, and if I have any issues, I will ask for help in a new post, so you can respond/help, or not.
No problem, either way...

Take care

 

[Macropod]

Please post an example file that contains the data as captured, as well as an example of the result that you want from the captured data.

Kindly read Part B of the forum Guidelines: https://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html. It is inappropropriate to recommend someone post an example file when that can't be done.