Sub Doit()
Dim wSheet As Worksheet
Dim X As Double
Dim DataArray(500, 10) As Variant
Dim NewWks As Worksheet
Dim TheStr As String
Sheets("Summary").Select
ActiveWindow.SelectedSheets.Delete
For Each wSheet In Worksheets
TheStr = wSheet.Name

Sheets(TheStr).Select
X = X + 1
DataArray(X, 1) = Range("I9")
DataArray(X, 2) = Range("C6") 'First Name
DataArray(X, 3) = Range("D6") 'Last Name
DataArray(X, 4) = Range("N6") ' ID
Next wSheet
Set NewWks = Worksheets.Add
NewWks.Name = "Summary"
Sheets("Summary").Select
For Y = 1 To X
For Z = 1 To 4
Cells(Y + 1, Z + 1).Value = DataArray(Y, Z)
Next
Next
Cells(1, 3).Value = "FName"
Cells(1, 4).Value = "LName"
Cells(1, 2).Value = "Status"
Cells(1, 5).Value = "ID"

Cells.Select
Cells.EntireColumn.AutoFit
Set PrtRng = Range(Cells(1, 1), Cells(5, X + 3))
With ActiveSheet.PageSetup
.Zoom = False
.PrintArea = PrtRng.Address
.PrintTitleRows = "$1:$1"
.Orientation = xlPortrait
.FitToPagesWide = 1
.FitToPagesTall = 10
End With
Cells(2, 1).Select
ActiveWindow.FreezePanes = True






End Sub


"HH" wrote:

Although this might be better handled with a true database - like Access,
Excel is the only choice I have now.

My workbook contains 50+ worksheets labeled 101 to 150. More worksheets
will likely be added. Each worksheet contains data on a single individual..

I want Excel to enter data on a summary worksheet (in the same workbook)
from each worksheet.

Specifically: Cell I9 on every worksheet is the status of he person
(active,inactive, etc). Cells C6, D6 and N6 on each sheet contain First
names, Last Names, and ID on each person. So if cell I9 status is
"active" I want cells B2 - B50(or more if sheets are added) on the new
summary sheet to contain the data in C6 of every sheet. C2-C50 would
contain data in D6 of each sheet, D2-D50 would contain data in N6 of each
sheet.

Thanks