Summary excel sheet for multiple sheets

[redbna]

I have an limited knowledge of Excel but am able to get around pretty well with formulas and such. I'm creating an attendance workbook for each employee and would like to have a summary page that will list out the employee info including the types and total absences. I have 200 sheets that I've formatted identically but in creating the summary sheet it's very tedious to "fill down" the cells since it won't roll to the next sheet, only the next cell.
I've seen similar examples out there but haven't been able to make heads or tails of it so thought I'd provide my information and hopefully I'm not duplicating a situation.

Here's some screen shots of the sheets:
https://plus.google.com/photos/103975417699361850835/albums/5694639275509253473

Thank you,
Nathan

 

[b.downey]

Copy the following macro to a VBA Module and execute. It will go thru each of the 200+ sheets (ignoring the "Summary" and "Blank" sheets) and post the relative information to the "Summary" Sheet.

Sub CreateSummary()
    Dim wsSum As Worksheet
    Dim wsEmp As Worksheet
    Dim ws As Worksheet
 
    Dim SumRowNo As Long
 
    Set wsSum = ThisWorkbook.Worksheets("Summary")
 
    SumRowNo = 9
 
    wsSum.Range("A9:Z1000").ClearContents
    For Each ws In Worksheets
        Select Case ws.Name
            Case "Summary", "Blank"
                'Ignore
            Case Else
                wsSum.Cells(SumRowNo, "A") = ws.Range("D1")
                wsSum.Cells(SumRowNo, "B") = ws.Range("D2")
                wsSum.Cells(SumRowNo, "C") = ws.Range("X1")
                ws.Range("AG18:AR18").Copy Destination:=wsSum.Cells(SumRowNo, "D")
 
                SumRowNo = SumRowNo + 1
        End Select
        MsgBox "Complete"
    Next
 
 
End Sub