VBA Code Needed

N

nhbartos

Board Regular
Joined
May 23, 2015
Messages
148
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]
 
Sort by date Sort by votes

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
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...

Code: 
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
 
Upvote 0
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.
[TABLE="width: 441"]
<tbody>[TR]
[TD="class: xl64, width: 205, bgcolor: transparent, colspan: 2"]April,1,2017
[/TD]
[TD="class: xl65, width: 106, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 92, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 92, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 92, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent, colspan: 2"]Student Attendance Record
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]Student Name:
[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"]Student 2
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent, colspan: 2"]Medicaid:
[/TD]
[TD="class: xl67, bgcolor: transparent"]N/A
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent, colspan: 2"]Date of Birth:
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]02/01/2000
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent, colspan: 2"]Admission Date:
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]03/05/2010
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent, colspan: 2"]Discharge Date:
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]02/08/2015
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]Sick Days
[/TD]
[TD="class: xl68, bgcolor: transparent"]Early Leave Days
[/TD]
[TD="class: xl68, bgcolor: transparent"]Late Arrival Days
[/TD]
[TD="class: xl68, bgcolor: transparent"]Vacation Days
[/TD]
[TD="class: xl68, bgcolor: transparent"]Half Days
[/TD]
[TD="class: xl68, bgcolor: transparent"]Late In Early out
[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]Sep 1 Days
[/TD]
[TD="class: xl68, bgcolor: transparent"]Jul 1 Days
[/TD]
[TD="class: xl68, bgcolor: transparent"]Jul 3 Days
[/TD]
[TD="class: xl68, bgcolor: transparent"]Jul 3 Days
[/TD]
[TD="class: xl68, bgcolor: transparent"]Jul 3 Days
[/TD]
[TD="class: xl68, bgcolor: transparent"]Jul 3 Days
[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: transparent"]Aug 1 Days
[/TD]
[TD="class: xl68, bgcolor: transparent"]Aug 3 Days
[/TD]
[TD="class: xl68, bgcolor: transparent"]Aug 3 Days
[/TD]
[TD="class: xl68, bgcolor: transparent"]Aug 3 Days
[/TD]
[TD="class: xl68, bgcolor: transparent"]Aug 3 Days
[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent"]Sep 1 Days
[/TD]
[TD="class: xl69, bgcolor: transparent"]Sep 3 Days
[/TD]
[TD="class: xl69, bgcolor: transparent"]Sep 2 Days
[/TD]
[TD="class: xl69, bgcolor: transparent"]Sep 3 Days
[/TD]
[TD="class: xl69, bgcolor: transparent"]Sep 3 Days
[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: transparent"]Oct 1 Days
[/TD]
[TD="class: xl68, bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl70, bgcolor: transparent"]Total Days 1
[/TD]
[TD="class: xl70, bgcolor: transparent"]Total Days 3
[/TD]
[TD="class: xl70, bgcolor: transparent"]Total Days 9
[/TD]
[TD="class: xl70, bgcolor: transparent"]Total Days 9
[/TD]
[TD="class: xl70, bgcolor: transparent"]Total Days 9
[/TD]
[TD="class: xl70, bgcolor: transparent"]Total Days 9
[/TD]
[/TR]
[TR]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:
Upvote 0
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.

Code: 
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)))
 
Upvote 0
Try this:-
Code: 
 '###############
   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
 
Upvote 0
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!!!
 
Upvote 0
Change the 18 to a 19 !!
Code: 
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
 
Last edited:
Upvote 0
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???

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Medicaid Students[/TD]
[TD="align: center"]July 2017[/TD]
[TD="align: center"]August 2017[/TD]
[TD="align: center"]....[/TD]
[/TR]
[TR]
[TD]Student 1[/TD]
[TD="align: center"]7/2/17[/TD]
[TD="align: center"]8/15/17
[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]7/4/17
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]7/5/17[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]7/6/17[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD]Student 2[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD]Student 3[/TD]
[TD="align: center"]7/15/17[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]7/28/17[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD]Student 4[/TD]
[TD="align: center"]7/12/17[/TD]
[TD="align: center"]8/1/17[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]7/13/17[/TD]
[TD="align: center"]8/2/17[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]7/14/17[/TD]
[TD="align: center"]8/3/17
[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]8/4/17[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]8/5/17[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD]Student 5[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
You must log in or register to reply here.

Similar threads

zoharb
  • Question Question
networkdays.intl in excel 2007
Replies
2
Views
283
MARK858
MARK858
F
  • Solved
How to change value from data validation based on value in col A?
Replies
5
Views
330
Eric W
Eric W
D
  • Question Question
Count days at work that were fridays
Replies
3
Views
112
Dave Patton
D
J
  • Solved
Formula Filled Dates
Replies
4
Views
478
Fluff
Fluff
K
  • Question Question
Advice on Data Set up to Chart for Ongoing Daily Metrics
Replies
0
Views
170
Kolchak74
K

Forum statistics

Threads
1,223,723
Messages
6,174,123
Members
452,546
Latest member
Rafafa

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top