sumif/vlookup

[Alberta Rose]

I have multiple worksheets that I need to roll up to a summary worksheet.
Columns include Employee name, Precontract Hrs, Contract hrs, Admin Hrs, etc.
(6 or 7 columns of numeric info). I need to create a new worksheet that will
go into the monthly worksheets and depending on employee name, go to that
rows' column cells and add the numeric values and bring them back to the
summary.

Thanks

 

[joel]

The code below should do everything. The code will create a worksheet (if
one doesn't exist) called Summary. The it will go through the entire
workbook and create the sumary sheet looking and the column headers in row 1
and the employee names in column A. The code will add each sheet hours to
the summary sheet.

Sub MakeSummary()

'create new worksheet call summary
Found = False
For Each Sht In Sheets
If Sht.Name = "Summary" Then
Found = True
Exit For
End If
Next Sht

If Found = False Then
Sheets.Add before:=Sheets(1)
Set SumSht = ActiveSheet
SumSht.Name = "Summary"
Else
Set SumSht = Sheets("Summary")
SumSht.Cells.ClearContents
End If


NewRow = 2
NewCol = 2
'start making summary shet
For Each Sht In Sheets
If Sht.Name <> "Summary" Then
LastRow = Sht.Range("A" & Rows.Count).End(xlUp).Row
LastCol = _
Sht.Cells(1, Columns.Count).End(xlToLeft).Column
For RowCount = 2 To LastRow
Employee = SumSht.Range("A" & RowCount)
Set c = SumSht.Columns("A").Find(what:=Employee, _
LookIn:=xlValues, lookat:=xlWhole)
If c Is Nothing Then
SumRow = NewRow
NewRow = NewRow + 1
Else
SumRow = c.Row
End If

For ColCount = 2 To LastCol
ColHeader = Sht.Cells(1, ColCount)
EHours = Sht.Cells(RowCount, ColCount)
'check if header exists in summary sheet
Set c = SumSht.Rows(1).Find(what:=ColHeader, _
LookIn:=xlValues, lookat:=xlWhole)
If c Is Nothing Then
SumSht.Cells(1, NewCol) = ColHeader
SumSht.Cells(SumRow, NewCol) = _
SumSht.Cells(SumRow, NewCol) + EHours
NewCol = NewCol + 1
Else
SumSht.Cells(SumRow, c.Column) = _
SumSht.Cells(SumRow, c.Column) + EHours
End If

Next ColCount
Next RowCount
End If
Next Sht

End Sub