Solved

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

Posted on 2007-03-27
8
221 Views
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
                      ws.Activate
                      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
                        fnd.EntireRow.Select
                           fnd.EntireRow.Copy
                           Sheets("Hidden Data").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
                      End If
                 End If
            Next
          End If
          Sheets("Status Report").Activate
          Application.ScreenUpdating = True
          Application.EnableEvents = True
     End Sub
0
Comment
Question by:josepharichard
  • 5
  • 3
8 Comments
 
LVL 4

Expert Comment

by:jennynover
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.
0
 

Author Comment

by:josepharichard
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.
0
 

Author Comment

by:josepharichard
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:

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

Return back to normal:

Range("A2").Select
    Range("A1:H4").Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range( _
        "B2"), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase _
        :=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, _
        DataOption2:=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.
0
 

Author Comment

by:josepharichard
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?
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 4

Accepted Solution

by:
jennynover earned 500 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!
0
 

Author Comment

by:josepharichard
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?
0
 
LVL 4

Expert Comment

by:jennynover
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!!!  :)
0
 

Author Comment

by:josepharichard
ID: 18827290
Well, actually, There was a way I got from WJRied that worked perfectly, solved the very problem I was having.

http:Q_22476337.html

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.
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Suggested Solutions

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

759 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now