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.

July

Dates of Absence

Fri

Sat

Sun

Mon

Tue

Wed

Thu

Fri

Sat

Sun

Mon

Tue

Wed

Thu

Fri

Sat

Sun

Mon

Tue

Wed

Thu

Fri

Sat

Sun

Mon

Tue

Wed

Thu

Fri

Sat

Sun

Student Name

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

Student 1

Student 2

Student 3

Student 4

Student 5

Student 6

Student 7

Student 8

Student 9

Student 10

Student 11

Student 12

Student 13 Is anyone able to help with this? Vince

<colgroup><col><col span="31"></colgroup><tbody>
</tbody>

 

[MickG]

Try this new code:-
https://app.box.com/s/bltkmdigsfp8to9wk7pvc80m43lbdjdm

 

[nhbartos]

Excellent! Thanks a lot! Looks great.

I just realized however, that there is potential for there to be two or more pages for each report.

So, is there a way to have the image/logo and rows 7 - 19 auto copy down or populate on every page as needed if there are a lot of dates?


In addition, I see one issue that I didn't see before.
Rows 20 and 21 are not formatted for a date like the others...3/31/17. They look like this: 42445.
Not sure if this is report formatting or month tabs formatting.


I am trying to get page # of # pages to print at the bottom. No luck.

This is the code I am working on...

Sub Consecutive_Page_Nums()Dim WS As Worksheet
    For Each WS In ActiveWorkbook.Sheets
       With WS.PageSetup
         .CenterFooter = "&8Page &P & of &N"
       End With
    Next WS
ActiveWorkbook.Worksheets.Select
ActiveWindow.SelectedSheets.PrintOut    'PrintOut
End Sub

 

[MickG]

I think this code is getting a bit out of hand and I don't really want to get into page layout (header/footers) bit, so I am suggesting that the dates on the Students sheet should be replaced with the number of days used for each activity per month.

This way the size of the Date areas on each sheet is limited to 12 rows, one for each month.
The resulting sheet would look something like the below.
What do you think ????
NB:- There is no wrapped text effect in the actual sheet.

April,1,2017
Student Attendance Record
Student Name:		Student 2
Medicaid:	N/A
Date of Birth:	02/01/2000
Admission Date:	03/05/2010
Discharge Date:	02/08/2015
Sick Days	Early Leave Days	Late Arrival Days	Vacation Days	Half Days	Late In Early out
Sep 1 Days	Jul 1 Days	Jul 3 Days	Jul 3 Days	Jul 3 Days	Jul 3 Days
	Aug 1 Days	Aug 3 Days	Aug 3 Days	Aug 3 Days	Aug 3 Days
	Sep 1 Days	Sep 3 Days	Sep 2 Days	Sep 3 Days	Sep 3 Days
			Oct 1 Days
Total Days 1	Total Days 3	Total Days 9	Total Days 9	Total Days 9	Total Days 9

<tbody>
</tbody>

 

[nhbartos]

No, that won't work.
I am required to list the dates on the reports.
Forget page numbers.

However, if you have had enough, say the word and I will look elsewhere for some support.
I appreciate everything your have done. You have gone well above and beyond!
Thank you! Thank you!


The LAST THING I need a little help with is below.
I updated most of this section of code, but it is not working.


I added a new column to the Base Info tab, Student ID, to Column B.
Medicaid, Date of Birth, Admission Date, and Discharge Date shifted right.
Discharge Date is now in column E.

On the reports, I need to:

Add "Student ID:" to row 14, and shift everything else down by one row.
And have the ID numbers auto pop from the Base Info tab like the the others.

I spent a couple of hours yesterday trying to do it with no luck.

My current code for this section is below.

Dim nNam As Range   With Sheets(K)
    Set nNam = Studata(K): oMax = 0
    .Range("A13").Resize(500, 100).ClearContents
    .Range("C13") = K ' This is Student Name, remove if not wanted
    .Range("A7") = Format(Now, "MMMM,d,yyyy")
    .Range("A10").Value = "Student Attendance Record"
    .Range("A10:C10").Merge
    .Range("A7:B7").Merge
    .Range("c16").HorizontalAlignment = xlLeft
    .Range("A19:F19").Font.Bold = True
    .Range("A7").Font.Size = 16
    .Range("A7").Font.Bold = True
    .Range("A7:b17").Font.Size = 12
    .Range("A7:b17").Font.Bold = True
    .Range("A13:A17").HorizontalAlignment = xlLeft
    .Range("A7").HorizontalAlignment = xlLeft
    .Range("A21:F22").NumberFormat = "dd/mm/yyyy"
   
    .Range("A13").Value = "Student Name:"


[B]    .Range("A14").Value = "Student ID:"[/B]
    .Range("A14:B14").Merge
    .Range("c14").Value = IIf(CDate(nNam.Offset(, 1)) = "00:00:00", "N/A", CDate(nNam.Offset(, 1)))
    
[B]    .Range("A15").Value = "Medicaid:"[/B]
    .Range("A15:B15").Merge
    .Range("c15").Value = IIf(CDate(nNam.Offset(, 2)) = "00:00:00", "", CDate(nNam.Offset(, 2)))
    
[B]    .Range("A16").Value = "Date of Birth:"[/B]
    .Range("A16:B16").Merge
    .Range("C16").Value = IIf(CDate(nNam.Offset(, 3)) = "00:00:00", "", CDate(nNam.Offset(, 3)))
    
[B]    .Range("A17").Value = "Admission Date:"[/B]
    .Range("A17:B17").Merge
    .Range("C17").Value = IIf(CDate(nNam.Offset(, 4)) = "00:00:00", "", CDate(nNam.Offset(, 4)))

[B]     .Range("A18").Value = "Discharge Date:"[/B]
    .Range("A18:B18").Merge
    .Range("C18").Value = IIf(CDate(nNam.Offset(, 4)) = "00:00:00", "", CDate(nNam.Offset(, 4)))

 

[MickG]

Try this:-

 '###############
   Dim nNam As Range
   With Sheets(K)
    Set nNam = Studata(K): oMax = 0
    .Range("A7").Resize(500, 100).ClearContents
    .Range("C13") = K ' This is Student Name, remove if not wanted
    .Range("A7") = Format(Now, "MMMM,d,yyyy")
    .Range("A10").Value = "Student Attendance Record"
    .Range("A10:B10").Merge
    .Range("A7:B7").Merge
    .Range("A7").Font.Size = 16
    .Range("A7").Font.Bold = True
    .Range("A13").Value = "Student Name:"
   
    .Range("A14").Value = "Student ID:"
    .Range("A14:B14").Merge
    .Range("c14").Value = IIf(nNam.Offset(, 1) = "", "N/A", nNam.Offset(, 1))
    
     .Range("A15").Value = "Medicaid:"
     .Range("A15:B15").Merge
     .Range("c15").Value = IIf(CDate(nNam.Offset(, 2)) = "00:00:00", "N/A", CDate(nNam.Offset(, 2)))
    
    .Range("A16").Value = "Date of Birth:"
    .Range("A16:B16").Merge
    .Range("c16").Value = IIf(CDate(nNam.Offset(, 3)) = "00:00:00", "No Data", CDate(nNam.Offset(, 3)))
    
    .Range("A17").Value = "Admission Date:"
    .Range("A17:B17").Merge
    .Range("C17").Value = IIf(CDate(nNam.Offset(, 4)) = "00:00:00", "No Data", CDate(nNam.Offset(, 4)))
    
    .Range("A18").Value = "Discharge Date:"
    .Range("A18:B18").Merge
    .Range("C18").Value = IIf(CDate(nNam.Offset(, 5)) = "00:00:00", "No Data", CDate(nNam.Offset(, 5)))
    
    .Range("A7:b18").Font.Size = 12
    .Range("A7:b18").Font.Bold = True
    .Range("A13:A18").HorizontalAlignment = xlLeft
    .Range("A7").HorizontalAlignment = xlLeft
 '##########
        For n = 0 To 10 Step 2 ' added items from dict Now 10 was 7

 

[nhbartos]

Great!.
Works perfect!

This little adjustment is my last request
I need the row 19 columns shifted down to row 20 so I have an empty row.

Thank you!!!

 

[MickG]

Change the 18 to a 19 !!

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 +[B][COLOR=#ff0000] 19,[/COLOR][/B] cc).Value = Dic(K)(n)(c) 'NB:- Start Od Dates shown Here as C+18
            
            oMax = Application.Max(oMax, c)
            Next c
        Next n

 

[nhbartos]

Thank you!

All set!

Best to you...

 

[MickG]

You're welcome

 

[nhbartos]

Hi MikeG,

I just found a bug.

All the totals at the bottom of the reports show "1", not zeros when there are no absences.

Are you willing to help?



I was just asked to also provide a new summary report that adds just the Medicaid Students to it and lists the absence dates by month.

If you had enough of me, just tell me and I will seek elsewhere. No problem.

Maybe Landscape so we can fit all the months on one row, and .5 Margins, 10 font???

Medicaid Students	July 2017	August 2017	....
Student 1	7/2/17	8/15/17
	7/4/17
	7/5/17
	7/6/17
Student 2	0	0
Student 3	7/15/17	0
	7/28/17
Student 4	7/12/17	8/1/17
	7/13/17	8/2/17
	7/14/17	8/3/17
		8/4/17
		8/5/17
Student 5	0	0

<tbody>
</tbody>