Creating Multiple Reports, One by One, for a List of Names

[webermail]

Hi!

I need help to create and print multiple reports for a list of names.
have a table with a column of names; after each name is a row of value
associated with the name, and above each value are a row of heading
and a row of dates.

My goal is to take each name and its associated rows of data (values
headings, dates), and reference it in an individual, customized repor
format, print the individual report, then go to the next name down th
column of names and generate and print a second individual report
etc., etc.

Also, I would like my reports to format the rows of data as columns
not as rows. However, changing from rows to columns as I go from th
data table to the individual reports is NOT so important if it cause
too much complexity. That is, the report could have rows of data
rather than columns.

I'm a new school teacher (teaching English) trying to take a tables o
student grades for each of my classes of students, and generat
individual progress reports. I have a lot of spreadsheet experience
but my experience with Macros is limited to the generating multipl
print macros.

Thanks for any help!!

Tom





would be formatted and printed as a single report.

2.




, one name at a time.

by extracting from a table data associated with a single namefrom
table for a sequential list of names. I'm trying to set up a repor
that

1. Extracts data from a table - in this case reformatting ROWS o
headings and values as COLUMNS (to make life easier I could continue t
keep as rows)

2. Then prin

 

[Charles]

Tom,

If you wish you can send me your spreadsheet, with info on how you wan
it and I'll try to help you.

Charles
(e-mail address removed)

 

[Debra Dalgleish]

If the headings are the same for each student, you could store the data
in one table, with student names in the first column, and values in the
subsequent columns. For example:

Name 9/15/04 9/30/04 ...
John Smith 55 72
Mary Jones 81 70

Then, create a report in Microsoft Word, with the format you want. Use
its Mail Merge feature to produce an individualized report for each
student. There's information on the Word MVP site:

http://www.mvps.org/word/FAQs/MailMerge/CreateAMailMerge.htm

And David McRitchie has instructions on his web site:

http://www.mvps.org/dmcritchie/excel/mailmerg.htm

 

[Dsuperc]

Try this
ub FanOut()
Dim ColHead As String
Dim ColHeadCell As Range
Dim iCol As Integer
Dim iRow As Long 'row index on Fan Data sheet
Dim lRow As Integer 'row index on individual destination
sheet
Dim NewWB() As Workbook
Dim Fsheet As Worksheet 'fan data worksheet (assumed
active)
Dim Answer As Variant
Dim i As Integer

Again:
ColHead = InputBox("Enter Column Heading", "Identify Column",
[H1].Value)
If ColHead = "" Then Exit Sub
Set ColHeadCell = Rows(1).Find(ColHead, lookat:=xlWhole)
If ColHeadCell Is Nothing Then
MsgBox "Heading not found in row 1"
GoTo Again
End If

Set Fsheet = ActiveSheet
ReDim NewWB(0)

iCol = ColHeadCell.Column
'loop through values in selected column
For iRow = 2 To Fsheet.Cells(65536, iCol).End(xlUp).Row
If Not BookExists(CStr(Fsheet.Cells(iRow, iCol).Value)) Then
ReDim Preserve NewWB(UBound(NewWB) + 1)
Set NewWB(UBound(NewWB)) = Workbooks.Add
NewWB(UBound(NewWB)).SaveAs Fsheet.Cells(iRow, iCol)
Set Dsheet = NewWB(UBound(NewWB)).Sheets(1)
Fsheet.Rows(1).Copy Destination:=Dsheet.Rows(1)
Dsheet.Name = CStr(Fsheet.Cells(iRow, iCol).Value)
Else
Set Dsheet = Workbooks(CStr(Fsheet.Cells(iRow, iCol).Value) &
".xls").Sheets(1)
End If
lRow = Dsheet.Cells(65536, iCol).End(xlUp).Row
Fsheet.Rows(iRow).Copy Destination:=Dsheet.Rows(lRow + 1)
Next iRow

Answer = MsgBox("Would you like all the fanned out files closed?",
_
vbQuestion + vbYesNo, UBound(NewWB) & " Files
Created")
If Answer = vbYes Then
For i = 1 To UBound(NewWB)
NewWB(i).Close savechanges:=True
Next i
End If

End Sub

Function BookExists(BookId As Variant) As Boolean

' This function checks whether a workbook exists, and returns
' True if it exists, False otherwise. BookId can be either
' a book name string or an integer number. For example:

' If BookExists(3) Then Workbooks(3).Close

' closes the third workbook in the workbooks collection, if it
exists.
' Similarly,

' If BookExists("Annual Budget") Then Workbooks("Annual
Budget").Close

' deletes the workbook named "Annual Budget", if it exists.

Dim Wb As Object
On Error GoTo NoSuch
Set Wb = Workbooks(BookId & ".xls")
BookExists = True
Exit Function
NoSuch:
If Err = 9 Then BookExists = False Else Stop

End Function