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]

My immediate thoughts are:-
If you already have sheets for "Student 1,2 etc then you change the names in all the month sheets, and run the code, You will get new sheets for the new students names and those old student names "Student 1,2 etc, won't disappear you need to remove those sheets manually !!!

I'll have a look at the other bit tomorrow !!

 

[nhbartos]

Wow, thanks.
I really appreciate it.
No pressure.

So, I just inserted all the names and fake ones through row 125 in the July tab.
I then copied the table, from the student name row down to row 125, and pasted in the other months.
Then I hit update.
I got a compile error?

Is my somehow copying the table messing things up?

Chat soon...

 

[MickG]

Try this updated file with student dates now set vertically.
https://app.box.com/s/8zinlh2pckkxsuegckusufchra2oe9ci

I not sure why you are getting an error, the only criteria for the code is that "B3 to AF3" have the numbers 1 to 31 and that the students names Begin in "A4" and down.
In the students sheets only the first two rows will have Data in from the code, from row 3 to 10 you can put what you like in there.
The resulting Dates start on row 11.

There should be no problem copying the data as long as you place in the correct rows as stated above.

 

[nhbartos]

Ok, thank you!

I just tested it again....

1. I copied the names in July monthly tab, in A4 down.
2. I hit update, and all the tabbed named appeared. Perfect.
3. I hit update again after doing some formatting to get ready to copy to the other months, and lost the student names again.


Also...The compile error seems to only happen when updating using the JUNE button.
All the others work.

 

[MickG]

On sheet June, Right click the sheet tab and select "View Code".
Vb window shows.

There is a "Typo" there.
It should read as below, you will see there is a "u" missing.
That should fix that, have another try !!!

Call StuUpdate

Not sure if that fixes this:-[

3. I hit update again after doing some formatting to get ready to copy to the other months, and lost the student names again.

 

[nhbartos]

The compile error is fixed! Thanks.

In July, I copied names into July A4 to A111.
I dragged/copied data row 4 down to 111 to populate the rest of the cells with existing data.
I formatted table rows 4 - 111 to my preference...highlighting, borders, fonts, etc...only.

Then, I copied July cells A4 to AF111 to August again, hit update on August, and lost the names on the tabs again.

I didn't touch rows 1-3 except to format.

 

[nhbartos]

MickG...

Things were working for a while, but I keep losing the student names on the tabs.
This time I added a student and deleted, and then hit update again, I lost the names.
I seem to lose them if I update more than once.

 

[nhbartos]

Hi MickG,

I hope fixing the tab names isn't too much of a big deal.

But, is there a way you can modify the student tab report template?

1. Move Line 11 to line 15.

2. Add to B5, right align: "Student Name:"
3. Add to B6, right align: "DOB:"
4. Add to B7, right align: "Admission Date:"
5. Add to B8, right align: "Discharge Date:"

6. Change font color of rows 1 and 2 to "white", background color, so I don't see it.

7. Change Leave Type S to: Sick Days
8. Change Leave Type V to: Vacation Days
9. Change Leave Type P to: Personal Days
10. Change Leave Type H to: Half Days

Is any of this possible, or have you had enough of me?
Well, anyway, I do appreciate this tremendously.

 

[MickG]

Try this:-
https://app.box.com/s/wgmser8nojsbhgdm22bqzl94q9b6toh8

NB:- In "C5" for all students I have added the students name, If not wanted see line in code for removal as below.

With Sheets(K)
    .Range("A15").Resize(500, 100).ClearContents
    .Range("B5:B8").HorizontalAlignment = xlToRight
   [COLOR=#FF0000] .Range("c5") = K ' This[/COLOR] is Student Name, remove if not wanted
    .Range("B5").Value = "Student Name:"
    .Range("B6").Value = "DOB:"

Things were working for a while, but I keep losing the student names on the tabs.
This time I added a student and deleted, and then hit update again, I lost the names.
I seem to lose them if I update more than once.

When you say "Lost the names" do you mean the sheet is removed or the name or something else.
What do you mean ???

I have now remove the data from the First 2 rows of student sheet, I think that's what you actually wanted !!!!
I've also modified the code to take account of any blank cells in column 1 of "Months" sheets , that might have been a problem
Have another go see if there is any improvement

 

[nhbartos]

Hi...

Ok, I will take a look shortly. Thank you so much for your time.

Disappearing names...
I paste my student names into the July and hit update, all the kids names appear on individual tabs.
Perfect. This is what I need.

However, when I update again for any reason, after formatting tables, or adding names to the rest of the months..all the student names on the tab revert back to "Student 1", "Student 2".....and so forth.
I lose the names on the tabs even though all the months now have the names listed.
I am sure that I have not made any changes other than formatting to the table or rows 1-3, and column 1.
Am I messing this up?


I am not sure if I mentioned this as well...

We populated the table with V's, S's, H's, and P's, to test the reporting tabs, and it worked great. All the dates appeared in chron.
However, to test the reports, I deleted some dates from the tables and hit update to see if the reports would update.
They did not. The original dates stayed on the reports.

Is this fixable as well?