Need a Genius: Find, copy, paste rows based on more than one criteria!

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"
               Exit Sub
          End If
          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 = "'"
                      End If
                      If Not fnd Is Nothing Then
                           Sheets("Hidden Data").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
                      End If
                 End If
          End If
          Sheets("Status Report").Activate
          Application.ScreenUpdating = True
          Application.EnableEvents = True
     End Sub
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

You already said it!  you are trying to create a relatinal database with forms and reports using Excel.  It is the wrong tool for the job.  That is why they invented Access!!!  :)

You can have the users input the list information in Excel (details of the incident) if you REALLY want, then create a link table to Access where you can generate the current status as a query/report - and show it as a datasheet (admittedly color management isn't as easy in Access - but you can do it)

But most "easily confused" users actually cant tell the difference between putting information into an Access table in Datasheet view, or putting it into Excel!  So I just don't see any reason to take such a tortured route when relational databases exist.  For a small amount of user adjustment, it will be way more supportable in the long run.
josepharichardAuthor Commented:
Well, at this point, this workbook is nearly completed, except for this one thing, to have to completely start over again in access, which I know even less about, would be too much in my case.  If it were completely impossible to do in access via VB, that's one thing, but if there's any type of code which could be used to do this in excel, that's what I'm looking for.
josepharichardAuthor Commented:
Okay, I found a possible solution, but not sure how to quite impliment it with the code I already have.

I noticed that if a sheet has more than one dated entry, just the first one the process comes to is the one that's copied and pasted over and then it goes to the next sheet.  So what I'm thinking is code to automatically change the sorting of the sheet right before the process is run and then change it back right afterwards.

Messing around with recording macros, I came up with the following to sort the way I need, then put it back:

Sort by date and time descending:

    Range("A1:H4").Sort Key1:=Range("A2"), Order1:=xlDescending, Key2:=Range( _
        "B2"), Order2:=xlDescending, Header:=xlGuess, OrderCustom:=1, MatchCase _
        :=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, _

Return back to normal:

    Range("A1:H4").Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range( _
        "B2"), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase _
        :=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, _

Now not only do I need to figure out how to put this into my above code in order to change the sorting right before the process of grabbing the dates is run, but then change it back after the process is run, but how to adjust these bits of code so that it re-sorts ALL the sheets in the book, except for the "Status Report" sheet.

Any takers on this one?  Help would be greatly appreciated, thank you.
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

josepharichardAuthor Commented:
Okay, if its easier, which I'm sure it is, is there a way to get the process being run by my first set of code to search the sheets for the correct date from the bottom of the sheet up instead of the other way around?  That way the first date it comes to will also be the correct time which will be the one it will copy and paste?
well that could work if there will *never* be more than an absolute maximum of 2 incidents in a day... otherwise it falls flat.  

... but Arghhhh I'd hate to come in after you to support this application!  I do get that this is not the answer you want to hear, but you are using a screwdriver to hammer a nail, and Access is really not hard at the level you need to achieve this (this would be basic access), plus it will allow this app to grow in functionality - which these kind of apps invariably do.  

But hey - good luck to you!

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
josepharichardAuthor Commented:
Okay, so let's just say, for the sake of argument, that access is a complete impossibility, is there any way possible, within excel to achieve the results I'm looking for that you know of?
thanks for the credit - even if it insn't what you wanted!  sorry there wasn't another way - but once you get going in Access I bet you won't look back!!!  :)
josepharichardAuthor Commented:
Well, actually, There was a way I got from WJRied that worked perfectly, solved the very problem I was having.


It solved my problem with if there was more than one date, I could have hundreds of the same date, and the proper date is the one that's pulled out for my report.  I'm quite sure access would be more expandable and all, but for what I needed this book for, what I have is the way its going to be for years to come, eventually i'll look into an access solution.  I had one before, but for some reason it just didn't fly with the users.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.