vba code for finding all rows across multiple sheets

[kittean]

Hello all !!

I KNOW this has to have been answered in forum... but I have spent hours on here and I just can not find it even though I thought I saw it. (ever have one of those days.

I am not an expert at all in VBA but I am in need of a code that will search multiple tabs (worksheets) for a single student

For example... we have our attendance kept in Excel. Each month has its own tab. We must report hours to the federal government by quarters. The classes are already set to calculate the hours, and I am only having to search the different classes. The hours for each student must be moved to a single "master" sheet. There will be multiple rows for each student because they are tied to a class code

Class ABS-009-101 has jack smith and jill thomas. this class was held sept to dec.
Class ABS-006-301 has these two students as well and was held from

The code has to look for jack smith across all of the different classes to first locate all instances... and then move a copy of the rows to the "master" sheet. I do not expect that I will have 1 row for each student with the information falling into place. that is too much to expect from this kind of program and would not be compliant with our required way of recordkeeping (its the fed government .. do i need to explain more that that?)

I am looking for my master sheet to say

Class Student Q1 Q2 Q3 Q4
ABS-009-101 Jack smith 5 10
ABS-009-101 Jill Thomas 12 20
ABS-006-101 Jack Smith 7 21
ABS-006-101 Jill Thomas 20 36

From here I can sort the students to put all of jacks hours together and all of jills ... and then enter what i need to into the federal government data base.


Does this all make sense of has hours of staring at codes taken its toll

 

[pbornemeier]

Welcome to the Board!

Please provide either an image (or exact description of which columns holds what data) of the worksheets that hold the class/student info and the desired Master sheet.

Are the column layouts for each month exactly the same?
Are there any blank rows within the data in any worksheet?

Do you want to enter the name of a particular student and have the program bring the appropriate lines to the master list, or have the program find all students and put them all on the master list?

Do you have any students with the same name? How do you distinguish between them?

Can you copy all of the months in a quarter to a single worksheet then sort by student and use subtotals to get the data?

I think that using a pivot table with multiple input worksheets would allow you to quickly develop the information you need.
This page explains how to use multiple worksheets in a single Pivot Table. It also contains links that offer many other tutorials on using Pivot Tables.