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!! )

Lindsey

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!! )
>
> Lindsey
>

Lindsey
Auto-populating as you suggest would be a nightmare of trying to
determine when an entry is completed (6 columns) and when and what entry has
been deleted. May I suggest something? On some sheet (your choice) have a
button. Whenever you want to generate this "complete list", you click on
that button. The related code (a macro) fires and first deletes everything
on the complete list sheet, then copies everything from every "fire" sheet
and pastes it into the "complete list" sheet. Done! Takes about 2-3
seconds.
Does that sound anything like what you want?
If it does, post back and tell me more about the layout of your "fire"
sheets, like what columns do you want copied, in what row does the data
start (below the headers if any), the layout of the "complete list" sheet
AND it's name. Also tell me the tab name of EVERY non-fire sheet in the
file. The macro will loop through ALL the sheets in the file and it needs
to know which sheets to ignore.
Also tell me how you want the "complete list" sheet sorted after all the
copying is done. HTH Otto
"Rewbie" <(E-Mail Removed)> wrote in message
news:3874153B-969B-4805-A0CA-(E-Mail Removed)...
>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!! )
>
> Lindsey
>