Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Autopopulate text from multiple tabs into list.
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Autopopulate text from multiple tabs into list.
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Autopopulate text from multiple tabs into list.
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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sorting text and numbers from multiple tabs | Excel Worksheet Functions | |||
Create multiple sheet tabs from multiple cells. | Excel Worksheet Functions | |||
Autopopulate a drop down list in Excel | Excel Discussion (Misc queries) | |||
autopopulate multiple users personal info | Excel Worksheet Functions | |||
Autopopulate with zero | Excel Worksheet Functions |