Summary worksheet

[HH]

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

 

[Mike H.]

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]

Mike,
Thanks for your help.

I not great at this but I assume I need to insert a sheet labeled "Summary"
right click on the tap, and insert the code. If so nothing happened.

I'm sure I need to do something else.

Side questions: If I insert another sheet to draw different data will that
affect the summany sheet code? i.e. will the code you gave try to draw from
the new sheet?

Hank

 

[HH]

Ok Mike,
I figured out where to put the code (module right).
It works great except for a couple of things.

1. The summary sheet should contain list of people that are "Active" in cell
I9 on each worksheet.
2. How can I add another worksheet without it being involved in the Summary
sheet?

I think if we solve #1 and I dont have the word "Active" in any other sheet
it won't be in the summary. Or is there a better way.
Thanks for your help

 

[Mike H.]

I am not sure I understand your questions. How can you ahve a list in one
cell, I9? I don't get it???? Also, the way I set the macro up is:
1. Delete the tab, Summary.
2. Collect all the data from every tab left in the sheet
3. create a new summary and put entries on it.

So to answer your question, unless I modified the code, all sheets, other
than summary, are picked up.