How to Output Data from Variable Ranges in Multiple Sheets into 1Report; Yikes

[6afraidbecause789]

Hi there - I’ve made a gradebook that other teachers will now use, but
we want to be able to print reports from it. Say if a teacher clicks
anywhere on a student’s row, how can Excel output that student’s data,
found in certain columns in that sheet as well as possibly 6 other
worksheets (7 periods in a day) into Word, another Excel sheet, or
some other format?

If you can help, here’s the layout of the workbook:
8 sheets total, named Period 1, Period 2, …, Period 7, and Fields
(where I put the HREF scores).

Col headings in sheets 1 through 7 occupy rows 8 – 12.

Student last and first names are in cols A and B, starting on row 13.

The same student may be on each Period’s sheet.

There is 1 row under each student’s row that also has data for that
student (2 rows per student).

Values of formulas to output are in cols E through Q, T, AH, and AI.

Values in a range to also output start at AR—a new col is added after
AR for each new assignment; scores are entered in the cells below.

Another range to output starts 3 columns after the last column used
for the assignments. A col will be entered each day with the date in
the header and daily points in cells down below.

Note that these 'ranges' will end up being in different columns in
different periods.

If this is possible, us teachers would also like it if we could output
reports for all students in the entire workbook at once, similar to a
mail merge.

Thank you for your consideration of this quandary ;-)

 

[Bernie Deitrick]

Hey Teacher,

Select the two cells with the student name of interest (for example, A15:B15) then run this: we can
fine tune it after you see what it outputs. I'm sure you will need headers somewhere, and right now
it just copies all the columns.

HTH,
Bernie
MS Excel MVP

Sub FindStudentValuesInWorkbook()
Dim c As Range
Dim firstAddress As String
Dim mySht As Worksheet
Dim LastName As String
Dim FirstName As String
Dim DataSht As Worksheet

LastName = Selection.Cells(1, 1).Value
FirstName = Selection.Cells(1, 2).Value

On Error Resume Next
Application.DisplayAlerts = False
Sheets(LastName & ", " & FirstName).Delete
Application.DisplayAlerts = True

Set DataSht = Sheets.Add(Sheets(1))
DataSht.Name = LastName & ", " & FirstName

For Each mySht In ActiveWorkbook.Worksheets
With mySht.Range("A:A")
Set c = .Find(LastName, LookIn:=xlValues, lookAt:=xlWhole)

If Not c Is Nothing Then
firstAddress = c.Address
Do
If c.Offset(0, 1).Value = FirstName Then
c.Resize(2, 255).Copy
DataSht.Cells(Rows.Count, 1).End(xlUp)(2, 2).PasteSpecial xlPasteValues
DataSht.Cells(Rows.Count, 1).End(xlUp)(2).Resize(2).Value = mySht.Name
GoTo Found:
Else
Set c = .FindNext(c)
End If
Loop While Not c Is Nothing And c.Address <> firstAddress
End If

End With
Found:
Next mySht

End Sub



Hi there - I’ve made a gradebook that other teachers will now use, but
we want to be able to print reports from it. Say if a teacher clicks
anywhere on a student’s row, how can Excel output that student’s data,
found in certain columns in that sheet as well as possibly 6 other
worksheets (7 periods in a day) into Word, another Excel sheet, or
some other format?

If you can help, here’s the layout of the workbook:
8 sheets total, named Period 1, Period 2, …, Period 7, and Fields
(where I put the HREF scores).

Col headings in sheets 1 through 7 occupy rows 8 – 12.

Student last and first names are in cols A and B, starting on row 13.

The same student may be on each Period’s sheet.

There is 1 row under each student’s row that also has data for that
student (2 rows per student).

Values of formulas to output are in cols E through Q, T, AH, and AI.

Values in a range to also output start at AR—a new col is added after
AR for each new assignment; scores are entered in the cells below.

Another range to output starts 3 columns after the last column used
for the assignments. A col will be entered each day with the date in
the header and daily points in cells down below.

Note that these 'ranges' will end up being in different columns in
different periods.

If this is possible, us teachers would also like it if we could output
reports for all students in the entire workbook at once, similar to a
mail merge.

Thank you for your consideration of this quandary ;-)

 

[6afraidbecause789]

Wow Bernie, thanks. I was surprised and pleased to see such code; it
appears to be working - which is very nice considering school starts
next week - yikes! Several things...

Yes, you're right about the headings - there are none. They may
occupy rows 8 - 12, with the most critical header on row 12.

This appears to be outputting all columns. At the end of a quarter, a
sheet may extend out to cols CZ, DA, or beyond, which will take up a
lot of paper. Certain cols will remain absolute; others will be
relative...

--Values of formulas (points possible, percent, letter grade, etc.) to
output are ONLY in cols E through Q, T, AH, and AI in each student's 2
rows.

--Values in a range to also output will, on each sheet, always start
at col AR (a new col is added after col AR for each new assignment;
scores are entered in the cells below).

--Another range to output starts exactly 3 columns after the last
column used for the assignments. This is where each day is entered
daily in a col; the date is put in the header (row 12), daily
activities may also be added in the header (rows 8 - 11), and daily
points and comments are put in each student's 2 rows below.

Note that these 'ranges' will end up spanning different columns in
different periods or sheets.

Question--Is there a way to output all the cols generated in one of
these 'student sheets' as delimited text into Microsoft Word, which
will then double space down and wrap when the data nears the right
page margin, say at 1 inch? If all of these student sheets could be
dumped (individually as by highlighting a name like above, or all
together through another menu macro) into a Word doc, with 1 student
per page, that would be great. Hope this isn't too taxing.

Thanks again,
Nick

 

[Bernie Deitrick]

Nick,

I think you should consider a different solution: setting up report templates for each Period.

Insert a new column A on each sheet, and in that column, enter a formula like this in A13 (whichever
row has your first student name)

=IF(B13="", A12 & " B", B13 & ", " & C13)

That will create a one cell name for each student (and a second for the second row) that can be used
to look up values.

Then insert a new sheet, a report template for that sheet, and name a cell StudName. Then enter
the student name of interest, in the form

Lastname, Firstname

Then for each report sheet, enter these formulas somewhere in a three cell block, like A3:A5, and
then drag to the right as far as you can.
These formulas will pull data based on the value in StudName

=INDEX('Period 1'!$12:$12,1,Column(D1))
=VLOOKUP(StudName,'Period 1'!$6:$10000,COLUMN(D1),FALSE)
=VLOOKUP(StudName & " B",'Period 1'!$6:$10000,COLUMN(D1),FALSE)

Then, select and drag the cells around the page to lay the values out the way that you want. Delete
the formulas that pull in unneeded data, and leave the formulas that will pull in data from the
future. Set the page print area, the formatting, and then you can pull in any student's data at any
time.

Data from all periods can be combined into one report, etc - the key it that it will all be formula
driven, linked to the entered student's name.

HTH,
Bernie
MS Excel MVP