how to update your current file with new data from the source file.

[Kumar Shanmugam]

I have file with source data (Sheet 1) which contains verification report cases. Loan Account no and verification status Negative/fraud. Fraud cases (Sheet 2) only fraud cases.
Now I want to update my Sheet 2 with new fraud cases that are present in Source data.

Currently I have to do vlookup in source data with fraud database sheet. Is there any other method that Fraud cases (Sheet 2) get automatically updated by searching the Loan Account no in Source data (Sheet 1). Please find attached the sample data. LAN Account no highlighted red in Fraud case (Sheet 2) are the new updated cases.

 

[Ashhu]

Kumar
Are you asking for auto update of fraud cases from Source data to Fraud tab. if yes, please see below thread where its copying confirmed status from first tab to second. The search string here is "Confirmed" in yours its "Fraud".
you can update code to suit your need else we will help you.

http://forum.chandoo.org/threads/index-match-formula.29233/

 

[Kumar Shanmugam]

Hi You are correct I want auto update of Fraud case to Fraud tab. but only new cases. I would be having source data in altogether different workbook. I want solutions in such a way that data in fraud tab should be updated with new fraud cases on mapping with the source data workbook.

Sample data _VBA solution file referred by you exports all the confirmed flights. I will not be able to distinguish what all are newly updated data in Worksheet sheet.

 

[Ashhu]

Your source data tab will be continuous or every time it will be new. I mean do you add new dump data after last cell or paste in A1 tab?

I think we need that info to understand when updating fraud data, whether code should search from A1 cell or from middle.

 

[Kumar Shanmugam]

Yes I would like to add new dump data from the source data. For eg I have source data workbook. It contains fraud cases reported till date. Now I have an Fraud tracker file of yesterday. Now I want update my fraud tracker file with newly reported fraud cases till date. To get this I have to do vlookup in the source data workbook with Fraud tracker file. if I get #NA value against LAN No, that cases would be newly reported fraud case. Please find attached the updated fraud tracker file. Instead of vlookup. I would like to have some other excel tools that would auto update the fraud tracker file.

 

[Ashhu]

Hi kumar

I have added code to highlight the new fraud details to turn into red colour from data tab. I understand every time you will fresh data in source, when you run this code, it will change color of fresh fraud details into red and previous ones to black.
with my limited expertise i have written this code, if you need further addition Expert in forum may help you.
Thanks

Paste this code in sheet 3

Sub CopyTasks()
Dim wsDest As Worksheet
Dim wsSource As Worksheet
Dim i As Long
Dim wsWord As String

Application.ScreenUpdating = False
Set wsSource = Worksheets("Source data")
Set wsDest = Worksheets("Fraud")
wsWord = "Fraud"

wsDest.Range("a:h").Font.Color = vbBlack

With wsDest
  '.Range("A2:E34").ClearContents

    For i = 1 To 100
        If wsSource.Range("f2:f100").Cells(i).Value = wsWord Then
            wsSource.Range("A2:A100").Cells(i).Resize(1, 7).Copy
            .Cells(.Rows.Count, "A").End(xlUp).Offset(1).PasteSpecial xlPasteValues
          .Cells(.Rows.Count, "A").End(xlUp).Resize(1, 7).Font.Color = vbRed
         
        End If
    Next i
End With
Application.CutCopyMode = False

End Sub

 

[Kumar Shanmugam]

Hi, Thanks for your revert on priority.

I don't know how to apply macro. but I will study the same from the forum. I will apply the macro code once I have studied the same.

Thanks once again.

 

[Ashhu]

i am sorry,
please find attached sheet, in Fraud sheet you will find button , click same twice you will understand.

 

[Kumar Shanmugam]

Thanks its working. My work is easier now.