Tracking items accross multiple spreadsheets.

[ZacCerrato]

I've had Excel fanatics - including a brilliant professor I once had - tell me that anything that can be done on Access could actually be done using Excel. Maybe you all can confirm or deny that for me; here's my situation:

I work for a very small insurance program. My team and I manage the application process. Generally, people send in applications and they either have everything - or they don't. When they don't we send them letters telling them what we need. Sometimes we send them several letters before we have enough to make a decision. After that, we either accept or reject their application and send them another letter.

Currently we keep several spreadsheets (that are tied to Word documents as mail-merges) that serve to create the letters and also keep a record of letters we have sent applicants. Each entry has the date the letter was sent, the applicant's name and address, and information about the decisions that were made.

As it is we (I) often times have to go back to reconstruct a chronological history of certain applicant's application process by opening a few different spreadsheets and doing Ctrl-F searches and, well, its really annoying. If I could set up the system with my limited computer program knowledge I would ideally have set up a simple Access database where each applicant was immediately assigned a unique number that could be tracked through the system. BUT, seeing as we currently have everything in Excel and my co-workers are terrified of Access - can you guys (and gals) think of anyway I could create some sort of spreadsheet where information from all the tables was dumped and collated? Anyway to collect and organize the information from all these spreadsheets would be super useful. Any suggestions?

Thanks, Zac

 

[pboltonchina]

Try this it brings up an input box for you to enter your search criteria. It creates a sheet called Search Results and copies all the found information to it. If the sheet is already there it clears the contents and copies new data.

Private Function SheetExists(Sheetname As String) As Boolean
    ' Returns TRUE if a sheet exists in the active workbook
    Dim x As Worksheet
        On Error Resume Next
            Set x = ActiveWorkbook.Sheets(Sheetname)
                If Err = 0 Then SheetExists = True _
                Else SheetExists = False
End Function

Sub FindAllSheets()
    Dim Found As Range, WS As Worksheet, LookFor As Variant
        LookFor = InputBox("Enter value to find")
            
            If LookFor = "" Then Exit Sub
            
            '   Clear or Add a Results sheet
            If SheetExists("Search Results") Then
              Sheets("Search Results").Activate
              Range("A2").Select
              Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
              Selection.ClearContents
              Range("A1").Select
             Else
                Sheets.Add After:=Sheets(Sheets.Count)
                ActiveSheet.Name = "Search Results"
            End If
        Application.ScreenUpdating = False
           For Each WS In ActiveWorkbook.Worksheets
                If WS.Name <> "Search Results" Then
                     Set Found = WS.Cells.Find(What:=LookFor)
                     If Found Is Nothing Then
                         Range("D5").Select
                     Else
                         Found.EntireRow.Copy Sheets("Search results").Cells(Rows.Count, "A").End(xlUp).Offset(1)
                         Found.EntireRow.Interior.Color = vbYellow
                     End If
                End If
            Next WS
            With Sheets("Search Results")
  Set rngLast = .Cells.Find(What:="*", After:=.Cells(1, 1), LookIn:=xlValues, SearchDirection:=xlPrevious, SearchOrder:=xlByRows)
  If rngLast Is Nothing Then
    MsgBox "Search item not found! Exiting Sub...": Exit Sub
  Else
    If rngLast.Row = 1 Then _
      MsgBox "Search item not found! Exiting Sub...": Exit Sub
  End If
End With

            Sheets("Search Results").Activate
            Columns("A:T").AutoFit
          
    Range("A1").Select
     Application.ScreenUpdating = True
                 
End Sub

 

[ZacCerrato]

Thank you for the response PBolton. Is there anyone out there gracious enough to explain how I would implement this?

(I'm not a computer guy, I'm just a young, not-technically retarted, highly-funtioning "end user". ie. I don't really know code, VBA, macros, etc.)

-Zac

 

[ZacCerrato]

Actually, since so many threads on this forum turn into discussions about macros and VBA - does anybody have a link that would walk me through how to do a kind of "first project" with VBA? I don't even know what program to open - where to enter code - where I would test to see if that code is working - etc.

Any help would be great...feel like my Excel learning has plateau-ed where I can do everything but VBA and macros.

 

[pboltonchina]

Open Excel and then hold the alt key down and press F11. Copy and paste my code into the big window on the right hand side of the screen and then click the big red cross in the top right hand corner. You will get a message box saying 'This command will stop the debugger', click ok. Under the 'View' menu, select 'Macros', 'View Macros'. When the box pops up, double click 'FindAllSheets' and hey presto.

 

[ZacCerrato]

Fist of all, I have to say how exciting it is to have opened Microsoft Visual Basic for the first time. How exciting! I'm ready to start exploring.

Second, here were the problems I ran into, maybe someone could help:
- When the program opened there was only a gray background on the right side of the screen (the majority of the screen). So, I went to the menu
"View" -> Code (F7)
Here a blank window popped up and I entered the code that you provided. It did not ask 'This command will stop the debugger'...so I was unable to accept this/ press an "okay" button. Instead, when I ran the macro, a search box appeared and I entered a name. Only one entry from my spreadsheets appeared (there are over 6 entries for this name) and there appeared a message which, paraphrased, said something like "Error 1004!..." There was only a button that read, "Close". After this it seemed the Macro was through running as nothing else happened. I feel so close!

Thanks in advance to any genii that respond!
-Zac

 

[pboltonchina]

What version of Excel are you using? Check your macro security and ensure macros are enabled.