Are you looking to keep a running history or just get the list for a given
date and report those, then allow the user to pick another date and get
another list? (I personally like the latter.)

Okay, here's the code you're going to need to write. We're going to build a
form that will take a date selection from the user and then return the list
of all the entries. I'm assuming you want all the columns returned from each
sheet with the matching dates.

So, I'm assuming that each sheet that you enter the data on look EXACTLY
alike - same range of cells, columns in the same order. I'm also assuming
the sheets of data are together and the sheet that you are making your date
selection on is the first sheet (so, Sheet1 is your data selection sheet and
all the data entry sheets follow that one). Finally, I am assuming the Date
column is actually in some date format.

Here goes:

1. Create a Form.
2. Put on the form:
(a) A combo box. Call it cmbDate
(b) Two buttons - one called cmdOK; the other cmdCancel
3. You can add a label for the combo box and have its caption be something
like "Select Date".
4. Copy and paste this code.

Private Sub cmdCancel_Click()
Unload Me
End Sub

Private Sub cmdOK_Click()

Dim FirstCell As String
Dim myAgency, myID, myType, myRequest As String
Dim myDate As Date

FirstCell = "B2"

'Clear out the report sheet.
Sheets(1).Activate
Range(FirstCell).Select

While ActiveCell.Value < ""

ActiveCell.EntireRow.Delete

Wend

'Now get the matching records.
For x = 2 To Sheets.Count

Sheets(x).Activate
Range(FirstCell).Select

While ActiveCell.Value < ""

If (CStr(ActiveCell.Offset(0, 4).Value) = cmbDate.Value) Then
myAgency = ActiveCell.Value
myID = ActiveCell.Offset(0, 1).Value
myType = ActiveCell.Offset(0, 2).Value
myRequest = ActiveCell.Offset(0, 3).Value
myDate = ActiveCell.Offset(0, 4).Value
ActiveCell.Offset(1, 0).Select
Sheets(1).Activate
ActiveCell.Value = myAgency
ActiveCell.Offset(0, 1).Value = myID
ActiveCell.Offset(0, 2).Value = myType
ActiveCell.Offset(0, 3).Value = myRequest
ActiveCell.Offset(0, 4).Value = myDate
ActiveCell.Offset(1, 0).Select
Sheets(x).Activate
Else
ActiveCell.Offset(1, 0).Select
End If

Wend

Next x

Sheets(1).Activate
Unload Me

End Sub

Private Sub UserForm_Initialize()

Dim x, y As Integer
Dim FirstCell As String
Dim Found As Boolean

On Error GoTo Hell

Application.ScreenUpdating = False

'Update this accordingly.
FirstCell = "B2"

'Clear out the list of dates
cmbDate.Clear
'cmbDate.AddItem "Hello"
'Get all of the possible dates.
For x = 2 To Sheets.Count

Sheets(x).Activate

'Goto First Cell on sheet.
Range(FirstCell).Select

'Loop through the rows of data until an empty cell is found.
While ActiveCell.Value < ""

'See if the date for this row is already in the combo list.
y = 0
While (y <= cmbDate.ListCount - 1) And (Not Found)
If (cmbDate.ListCount = 0) Then
y = cmbDate.ListCount + 1
ElseIf (CStr(ActiveCell.Offset(0, 4).Value) =
cmbDate.List(y, 0)) Then
Found = True
Else
y = y + 1
End If
Wend

If (Not Found) Then
cmbDate.AddItem ActiveCell.Offset(0, 4).Value
End If

'Move to next row.
ActiveCell.Offset(1, 0).Select

Wend

Next x

Hell:

Application.ScreenUpdating = True

End Sub


5. You will need to change the "FirstCell" to be whatever your first cell is.

This will put on the first sheet all of the matching rows from the other
sheets.

"Rewbie" wrote:

I may have read elsewhere here that this is not possible, but I want to see
if my situation applies.

I manually track fire resources in CA that are sent to wildfires in one
workbook. Each fire has its own tab. Each tab has a list of 6 columns with
a resource's agency codes, id, type, request number, and date assigned. The
Chief wants a complete list every day of all resources assigned, which I have
to go to each tab and copy/paste the data entered into a sheet for a complete
list. Is there a way to create a sheet that will auto-populate a resource as
its entered on a fire tab so that, by the end of the day, all the resources
entered and/or removed from the fire tabs will be on this one sheet in a nice
long list?

Right now we have over 30 fires going, so this would make my life so much
easier! Any suggestions (even if it means another program) - I would greatly
appreciate it!! :o)

Lindsey