[Webinar] Streamline your web hosting managementRegister Today


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

Posted on 2007-03-27
Medium Priority
Last Modified: 2013-11-25
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
Question by:josepharichard
  • 5
  • 3

Expert Comment

ID: 18802553
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.

Author Comment

ID: 18802864
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.

Author Comment

ID: 18803739
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 new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.


Author Comment

ID: 18804008
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?

Accepted Solution

jennynover earned 1500 total points
ID: 18804042
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!

Author Comment

ID: 18804708
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?

Expert Comment

ID: 18827175
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!!!  :)

Author Comment

ID: 18827290
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.

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

612 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question