Chuck,

My standard advice in this type of situation is to bite the bullet, and combine all the sheets into
one sheet, with an additional column that contains the original sheet name.  Using multiple tabs for
the same type of data is simply bad design - use one database (on one tab), and then filter or Pivot
the data to get the view that you want.

There are many examples of macros that you can use to combine your data tables.  Below is one, with
the assumption that the data tables start in cell A1 of each sheet and are contiguous - no entirely
blank rows or columns. Also, the heading should be in row 1, the same for every sheet.

HTH,
Bernie
MS Excel MVP

Sub ConsolidateSheetsIntoDataBase()
With Application
  .DisplayAlerts = False
  .EnableEvents = False
  .ScreenUpdating = False
End With

Dim i As Integer
Dim myDB As Worksheet

Set myDB = Worksheets.Add(Before:=Worksheets(1))
myDB.Name = "DataBase Sheet"

Worksheets(2).Cells.Copy Worksheets(1).Cells

With Worksheets(1)
  .Cells(1, 1).EntireColumn.Insert
  .Cells(1, 1).Value = "Department"
  Intersect(.Range("A2:A" & .Rows.Count), _
            .UsedRange).Value = Worksheets(2).Name
End With

For i = 3 To Worksheets.Count
  With Worksheets(1)
     myRow = .Cells(Rows.Count, 1).End(xlUp).Row + 1
     Worksheets(i).Cells(1, 1).CurrentRegion.Offset(1) _
           .Copy Worksheets(1).Cells(myRow, 2)
     Intersect(.Range("A" & myRow & ":A" & .Rows.Count), _
               .UsedRange).Value = Worksheets(i).Name
  End With
Next i

With Application
  .DisplayAlerts = True
  .EnableEvents = True
  .ScreenUpdating = True
End With

End Sub