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>

 

[snjpverma]

How does your source data look like ? Please post an example of that also

 

[nhbartos]

Do you mean code? I have no code.
That is what I am looking for help on...

I need code that pulls the Dates of any V, S, P or H's entered in each monthly table, then transfers those dates into a table in a separate tab named for each specific student.
So, Student 1 will have his own report tab that has all the dates he was S, V, P, or H.

Something like this:

Vacation Taken	Sick Taken	Personal Taken	Half Day Taken
1/15/2017	2/25/2017	6/5/2017	1/19/2017
3/9/2017	4/1/2017		2/22/2017
6/5/2017			5/15/2017

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

Possible? Too complicated?
Make any sense?

 

[ExcelSpeedster]

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.

 

[MickG]

Please try this example file:-
https://app.box.com/s/qk9p96uf403nuiy6f4uawnuaezck6142

 

[nhbartos]

Thanks MickG...

This looks great!
The only thing I need to do is set up a separate tab for each student at the end of the workbook so I can link the dates into a report.

All students need their own reporting tab.
So, your note said no more worksheets. Can I add worksheets to the end, or the beginning?

Also, I would like to know how I would add students.
Even though there are not many now, I may have up to 125.

 

[nhbartos]

Hi ExcelSpeedster!

MickG provided me this link and it will work except for a few things...
It is safe.

In addition to what he provided, I need to do is set up a separate tab for each student at the end of the workbook so I can link the dates into individual report summaries.
The Students Leave tab is a great overall summary, but all students need their own reporting tabs as well.

However, his note said no more worksheets, but I do need to add worksheets to the end.

Also, I would like to know how I would add students.
I may have up to 125.



I would like each individual reporting tab to look like this:

Student 1:
DOB:
Admission Date:
Discharge Date:

Vacation Taken	Sick Taken	Personal Taken	Half Day Taken
1/15/2017	2/25/2017	6/5/2017	1/19/2017
3/9/2017	4/1/2017		2/22/2017
6/5/2017			5/15/2017

<tbody>
</tbody>

https://app.box.com/s/qk9p96uf403nuiy6f4uawnuaezck6142

 

[nhbartos]

MickG...

I just noticed that Fri for all tabs is above the students name, so the dates are off.
I added a column for it, but it is not included in the calculations...

Also, as I add or delete entries from the monthly sheets the Students Leave tab does not update.

Any change that the dates on the Student Leave tab can populate in order by date, earliest to latest?

 

[MickG]

Try this:-
The formatting need tidying up a bit !!!
https://app.box.com/s/8mko7s6aarzbg1v6vinvooj0lb5gbnz3

NB:-
The Code collects Student Leave Dates and places them on Individual sheets for each student Name.
To run the code each sheet now has a individual Button that when clicked will update all Students sheets.
If the Sheet for "Student name" does not exist the code will create it.
Important Things in this Code/Data:-
1) Column (1) for (Students names) and Row (3) with numbers 1 to 31, must stay the same basic
layout for all Month_Year sheets
3) Sheet Names (Month_Year) to stay the same
4) Leave type Initial to be Uppercase.
5) You can create new sheets if required, but any name must NOT have a "_" in it !!!!
6) The days "Names" shown in row 2 are not used by the code. I just copied them over from the thread
7) The Leave dates are collected as the code loops through each Months sheet so the dates should be
automatically in Chronological order ???

 

[nhbartos]

MickG...

Thanks again!

So, far you are getting what I want...

Problem:

In July, I copy and paste student names into column 1, all the way down to row 125.
I click the update button, and all the kids names appear on individual tabs. Perfect.

But, when I paste the names into August, and the remaining months, I lose the names on the tabs.
They revert back to Student 1, Student 2, etc...

If it is not too much trouble, also...
On the individual student tabs, is it possible to insert the dates in columns, in chron order, and not in rows, and maybe start them on row 10?
I need these to print on a single portrait page.