Need a Genius: Find, copy, paste rows based on more than one criteria!
Posted on 2007-03-27
This is what I have for a process to enter a date into Cell "G3" of sheet "Status Report" and, using that date, search the other sheets in the workbook for that date in column A of the other sheets and copy the entire row into a sheet, "Hidden Data." The process also takes into consideration if one of the sheets does not have the required date, at which time a row is skipped where that row would go (if the sheet did have the date) on the "Hidden Data" sheet (Thanks to WJReid for that much necessary change). The change I need now is to somehow account for if a sheet has more than one entry for a specific date. There is a second column, column "B" that holds the time for the entry, and what I need in this process is that when searching through the sheet for the date, if the process encounters a sheet with more than one of the same date, the row with the most recent time is the one that is copied and pasted to the "Hidden Data" sheet.
The purpose of this sheet, if background information helps is to track the system statuses of certain pieces of equipment. Each piece has its own sheet. On the Status Report sheet a user enters a date and they can quickly, very colorfully organized, view the system statuses of the entered date, thus the "hidden data" sheet which acquires all the information for the date entered so that the Status Report sheet can draw the information from there to enter them into the necessary spots on the Status Report sheet. Some days the status of a peice of equipment may change more than one time, for instance, in the morning, when logged, it may be "Green", but later in the afternoon, It changes to "Red." Thought its necessary to track these changes on the individual sheets, for the report only the actual current status of the equipment at the time is necessary, so only whatever row has the most recent information for that date needs to be copied over to the Hidden Data sheet. I'm sure all this would have been much easier to do in Access, but the users this is intended for are already using excel for a different workbook, so they are familiar with using excel, and I don't want to confuse them (they are easily confused).
Thank you all in advance for your assistance, and again to all those here who've helped with this workbook so far. This is it, this one change and finally this book will be deployable.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> [G3].Address Then Exit Sub
If Not IsDate(Target.Value) Then
MsgBox "Value entered into " & Target.Address & " is not a date"
If Target.Value > 0 Then
Sheets("Hidden Data").Range("A1").Value = Target.Value
Dim ws As Worksheet
Dim fnd As Range
Application.ScreenUpdating = False
Application.EnableEvents = False
For Each ws In ThisWorkbook.Worksheets
If ws.Name <> "Status Report" And ws.Name <> "Hidden Data" Then
Set fnd = ws.Range("A:A").Find(Target.Value, , xlValues, xlWhole)
If fnd Is Nothing Then
Sheets("Hidden Data").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Value = "'"
If Not fnd Is Nothing Then
Sheets("Hidden Data").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Application.ScreenUpdating = True
Application.EnableEvents = True