Copy date from seperate workbook using autofilter / search.

[pg1987]

Hi Guys / Gals!

Long time reader of MrExcel, but only just registered - Most of my questions get answered here without the need to post, just proves how resourceful this place is

Ok, I am having an issue, I have prepared a sample sheet for you, so you can see what I'm trying to edit...

I have created a SS with multi functionality for myself to monitor non-compliance with policy. So far, it tracks and records all data I input and makes my life a heck of a lot easier. Half of the code however, was compiled by some one who isn't available to clarify his own code and I just dont touch it (it aint broke, I aint gotta fix it!)

I have added an "email generator", which works well and generates an email at the click of a button using the variables from the SS - excellent.

One criticism that I have, and want to fix is giving me a massive headache.

Here is the code and I have highlighted where I want the edit:

Private Sub  CommandButton1_Click()
'-------------------------------------
'CONFI DATA  EMAIL SCRIPT/CODE
'-------------------------------------
   
    Dim  emDate, emName, emOffence, emAction As String
    Dim emSubject As  String
    Dim cpy As New DataObject
    'Dim emailrequirement As  String
    
    emDate = Worksheets("2013").Range("D2")
    emName =  Worksheets("2013").Range("T2")
    emOffence =  Worksheets("2013").Range("E2")
    emAction =  Worksheets("2013").Range("G2")
    emSubject = emName & " - " &  emOffence & " - " & emAction & " required" & " - " & emDate  & " - SMB"
    
    'Cases - identify email needed
    Select Case  emAction
         'Feedback Email
        Case "Feedback"
                 
                cpy.SetText emSubject & vbNewLine & vbNewLine &  "Hi, " & vbNewLine & vbNewLine & "During a Security Walk on the "  & emDate & ", unattended confidential data was found on " & emName  & "'s desk (please see attached file). Leaving confidential information  unattended is contrary to both ISO27001 standards and Capita policy. " &  vbNewLine & vbNewLine & "Please provide feedback ASAP stressing the  importance of keeping confidential data secure at all times, please note that  recurrence within 6 months may lead to further action being required." &  vbNewLine & vbNewLine
    
                cpy.PutInClipboard
     
                MsgBox "Confi Data Feedback email has been created and  copied to clipboard" & vbNewLine & vbNewLine & "Please remember to  attach the evidence before sending."
                
        ' Documented  discussion email
        Case "Documented Discussion"
             
                cpy.SetText emSubject & vbNewLine & "Hi," &  vbNewLine & vbNewLine & "During today's security walk (" & emDate  & "), unattended confidential data (see attached file)  was found on this  agent's desk. As you are aware, leaving confidential information unattended is  contrary to both ISO27001 standards and Capita policy." & vbNewLine &  vbNewLine & "This is the second occasion within 6 months that unattended  confidential data has been found relating to " & emName & ", the first  occasion was on the 
 Private Sub  CommandButton1_Click()
'-------------------------------------
'CONFI DATA  EMAIL SCRIPT/CODE
'-------------------------------------
   
    Dim  emDate, emName, emOffence, emAction As String
    Dim emSubject As  String
    Dim cpy As New DataObject
    'Dim emailrequirement As  String
    
    emDate = Worksheets("2013").Range("D2")
    emName =  Worksheets("2013").Range("T2")
    emOffence =  Worksheets("2013").Range("E2")
    emAction =  Worksheets("2013").Range("G2")
    emSubject = emName & " - " &  emOffence & " - " & emAction & " required" & " - " & emDate  & " - SMB"
    
    'Cases - identify email needed
    Select Case  emAction
         'Feedback Email
        Case "Feedback"
                 
                cpy.SetText emSubject & vbNewLine & vbNewLine &  "Hi, " & vbNewLine & vbNewLine & "During a Security Walk on the "  & emDate & ", unattended confidential data was found on " & emName  & "'s desk (please see attached file). Leaving confidential information  unattended is contrary to both ISO27001 standards and Capita policy. " &  vbNewLine & vbNewLine & "Please provide feedback ASAP stressing the  importance of keeping confidential data secure at all times, please note that  recurrence within 6 months may lead to further action being required." &  vbNewLine & vbNewLine
    
                cpy.PutInClipboard
     
                MsgBox "Confi Data Feedback email has been created and  copied to clipboard" & vbNewLine & vbNewLine & "Please remember to  attach the evidence before sending."
                
        ' Documented  discussion email
        Case "Documented Discussion"
             
                cpy.SetText emSubject & vbNewLine & "Hi," &  vbNewLine & vbNewLine & "During today's security walk (" & emDate  & "), unattended confidential data (see attached file)  was found on this  agent's desk. As you are aware, leaving confidential information unattended is  contrary to both ISO27001 standards and Capita policy." & vbNewLine &  vbNewLine & "This is the second occasion within 6 months that unattended  confidential data has been found relating to " & emName & ", the first  occasion was on the " & emDate & ", therefore a documented discussion  will be required." & vbNewLine & vbNewLine & "Please can you  complete the attached documented discussion form and send a completed copy to  SAAT within 48 hours. Please also be aware that a recurrence within 6 months may  lead to further action being required." & vbNewLine & vbNewLine
     
                cpy.PutInClipboard
        
                MsgBox  "Confi Data Documented Discussion email has been created and copied to  clipboard." & vbNewLine & vbNewLine & "Please remember to create and  attach the appropriate Documented Discussion, evidence and amend  dates."
        
        'Investigation email
        Case "Referal to  Disciplinary"
        
                cpy.SetText emSubject &  vbNewLine & "Hi," & vbNewLine & vbNewLine & "During today's  security walk " & emDate & ", unattended confidential data (see attached  file)  was found on " & emName & "'s desk. Leaving confidential  information unattended is contrary to both ISO27001 standards and Capita  policy." & vbNewLine & vbNewLine & "This is the second occasion  within 6 months of a Documented Discussion for" & emName & " (who  received a Documented Discussion for Confi Data on DATE), therefore an  Investigation will be required." & vbNewLine & vbNewLine & "Please  can you schedule an investigation within the next 24 hours to ensure the meeting  takes place within 48 hours.  If there is any reason you are unable to do this  please let us know at your earliest convenience and advise SAAT of the outcome  when it has taken place." & vbNewLine & vbNewLine _
                 & "If you require any further information or support from SAAT, please do  not hesitate to get in touch."
            
                 cpy.PutInClipboard
            
                MsgBox "Confi Data  Referral to disciplinary email has been created and copied to your clipboard."  & vbNewLine & "Please remember to insert the previous dates and evidence  to the email content before sending."
    End Select
End Sub
  ", therefore a documented discussion will be required." & vbNewLine  & vbNewLine & "Please can you complete the attached documented  discussion form and send a completed copy to SAAT within 48 hours. Please also  be aware that a recurrence within 6 months may lead to further action being  required." & vbNewLine & vbNewLine
    
                 cpy.PutInClipboard
        
                MsgBox "Confi Data Documented  Discussion email has been created and copied to clipboard." & vbNewLine  & vbNewLine & "Please remember to create and attach the appropriate  Documented Discussion, evidence and amend dates."
        
         'Investigation email
        Case "Referal to Disciplinary"
         
                cpy.SetText emSubject & vbNewLine & "Hi," &  vbNewLine & vbNewLine & "During today's security walk " & emDate  & ", unattended confidential data (see attached file)  was found on " &  emName & "'s desk. Leaving confidential information unattended is contrary  to both ISO27001 standards and Capita policy." & vbNewLine & vbNewLine  & "This is the second occasion within 6 months of a Documented Discussion  for" & emName & " (who received a Documented Discussion for Confi Data  on [B]DATE[/B]), therefore an Investigation will be required." &  vbNewLine & vbNewLine & "Please can you schedule an investigation within  the next 24 hours to ensure the meeting takes place within 48 hours.  If there  is any reason you are unable to do this please let us know at your earliest  convenience and advise SAAT of the outcome when it has taken place." &  vbNewLine & vbNewLine _
                & "If you require any further  information or support from SAAT, please do not hesitate to get in  touch."
            
                cpy.PutInClipboard
             
                MsgBox "Confi Data Referral to disciplinary email has been  created and copied to your clipboard." & vbNewLine & "Please remember to  insert the previous dates and evidence to the email content before  sending."
    End Select
End Sub

Ok, So all the above does is use the Case function to find out what type of email it needs to create, this is just 1 of the 5 seperate pieces of similar code it uses ( this can all be seen in the spreadsheet code when you get the sample).

All I need to do, is for the "Documented Discussion email" is to pull the previous occasion (when the first event "feedback" occurred.

And on the "investigation email" it pulls the dates for the Documented discussion and the feedback.

If any one can help, I would be highly grateful and in your debt.

Many thanks for reading

Erm, it won't let me attach a file


Paul
Excel Newb