Check & Update existing rows of data to fill in the blanks

[Kemidan2014]

To the Credit of DanteAmor who helped me with this code I thought i would start a new thread since my original problem was solved but i need to expand on this
Below is the WORKING code that automates a task i need to do which is Pull from a designated file that we download from customers website and name it the same name every time
This macro does its job and finds the new records and adds them to the bottom of our Master complaint Tracking spreadsheet which is a separate workbook that contains all the macros.

I tested if it would find newly added Dates in one of the columns for records that had been existing and failed to pick them up OR if the STATUS changed in one of the columns it would not recognized this

how could we expand on this so that it looks at the older records to see if there are changes to them and updates the data in the destination worksheet?

Sub Filter_Data()
  Dim lr As Long
  Dim srcSH As Worksheet, desSH As Worksheet
 
  Application.ScreenUpdating = False
  Workbooks.Open ("O:\1_All Customers\Current Complaints\ToyotaData.xlsx")
  Set srcSH = ActiveWorkbook.Sheets("Data")
  Set desSH = Workbooks("Customer Database Test 2.xlsm").Worksheets("sheet1")
 
  With srcSH
    If .AutoFilterMode Then .AutoFilterMode = False
    lr = .Range("A" & Rows.count).End(3).Row
    .Range("AN2:AN" & lr).Formula = "=IFERROR(IF(MATCH(A2,'[" & desSH.Parent.Name & "]" & desSH.Name & "'!$A:$A,0),""true"",""false""),""false"")"
    .Cells(1).CurrentRegion.AutoFilter 40, "false"
    If srcSH.Range("A" & Rows.count).End(3).Row > 1 Then
      .Range("A2:B" & lr & ",D2:E" & lr & ",H2:K" & lr & ",O2:O" & lr & ",Q2:AB" & lr & ",AH2:AH" & lr).SpecialCells(xlCellTypeVisible).Copy
      desSH.Cells(.Rows.count, "A").End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
      desSH.Columns.AutoFit
    End If
    .Range("A1").AutoFilter
    .Columns("AN").ClearContents
  End With
  srcSH.Parent.Close False
  Application.ScreenUpdating = True
End Sub

SOURCE SHEET

ToyotaData2.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM
1	QIMS#	Doc Type	Instance Handle Key	Rank	Supplier Code	TMMC Supplier Code	Supplier Name	Part Name	Part Number	Overall Status	NCD Description	QEName	Full Name	Full Name	Model	SQA Rank	LT CM Plan Due	Original LTCM Actual Due Date	Revised LTCM Actual Due Date	Close	Initial Issuance Date	Official Issuance Date	LTCM Plan Submitted	LTCM Plan Accepted Date	LTCM Plan Rejection Date	LTCM Actual Submitted	LTCM Actual Accepted Date	LTCMActualRejectionDate	Occurence	Where NC found?	Will Parts Be Quarantined ?	Why Made Category	Why Shipped Category	NAMC	ST/CM Status	LT/CM Status	Standard	Actual	Assigned To
2	01-01016-V6-1111	QPR	Irrelevent Data	B	0101-6		Aisin Automotive Casting - Tennessee	Part 1	11111	Closed-Cancelled	FIT CONDITION	QE	AM	MGR	V6					5/3/2021	4/28/2021								First Time	GR-KAI	false			CUST 1	STCM Received		GASKET HAS TO BE SEATED ALL THE WAY AROUND, CANNOT BE STICKING UP	HEAD COVER GASKET NOT SEATED	205519
3	01-01016-V6-2222	QPR	Irrelevent Data	B	0101-6		Aisin Automotive Casting - Tennessee	Part 2	22222	Awaiting Official, STCM Approved	LOOSE	QE	AM	MGR	V6					5/3/2021	4/28/2021								First Time	GR-KAI	false			CUST 1	STCM Received		GASKET MUST STAY IN PLACE	GASKET OUT OF GROOVE	205519
4	01-01016-V6-3333	QPR	Irrelevent Data	C	0101-6		Aisin Automotive Casting - Tennessee	Part 2	11111	Officially Released, Awaiting LTCM Plan	POPPING / PULLING OUT	QE	AM	MGR	V6					5/3/2021	4/28/2021								First Time	GR-KAI	true			CUST 1	STCM Received		GASKET TO BE SEATED AND NOT "OUT" OF THE GASKET GROOVE	GASKET IS NOT SEATED WHEN CONVEYANCE IS LOADING THE PART	205519
5	01-01016-V6-4444	QPR	Irrelevent Data	B	0101-6		Aisin Automotive Casting - Tennessee	Part 3	33333	Officially Released, Awaiting LTCM Plan	WRONG PART	QE	AM	MGR	V6					3/8/2022	3/7/2022								First Time	NONC	true			CUST 1	Awaiting STCM		Correct bolt with 8 marking	Wrong bolt with no 8 marking	205519
6	0101-6-080-5555	QPR	Irrelevent Data	B	0101-6		Aisin Automotive Casting - Tennessee	Part 3	33333	Officially Released, Awaiting LTCM Plan	SCRATCHES	QE	AM	MGR	I4					9/25/2017	9/15/2016								Recurrence	AR Upper Line	true			CUST 2			Parts must meet all specs.	Part had scratch on seal surface.
Data

DESTINATION SHEET BEFORE UPDATE

Customer database test 2.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1	QIMS#	Doc Type	Rank	Supplier Code	Part Name	Part Number	Overall Status	NCD Description	Model	LT CM Plan Due	Original LTCM Actual Due Date	Revised LTCM Actual Due Date	Close	Initial Issuance Date	Official Issuance Date	LTCM Plan Submitted	LTCM Plan Accepted Date	LTCM Plan Rejection Date	LTCM Actual Submitted	LTCM Actual Accepted Date	LTCMActualRejectionDate	NAMC
2	01-01016-V6-1111	QPR	B	0101-6	Part 1	11111	Officially Released, Awaiting LTCM Plan	FIT CONDITION	V6					4/28/2021	4/29/2021							CUST 1
3	01-01016-V6-2222	QPR	B	0101-6	Part 2	22222	Awaiting Official, STCM Approved	LOOSE	V6				5/3/2021	4/28/2021	4/29/2021							CUST 1
4	01-01016-V6-3333	QPR	C	0101-6	Part 2	11111	Officially Released, Awaiting LTCM Plan	POPPING / PULLING OUT	V6				5/3/2021	4/28/2021	4/29/2021							CUST 1
5	01-01016-V6-4444	QPR	B	0101-6	Part 3	33333	Officially Released, Awaiting LTCM Plan	WRONG PART	V6				3/8/2022	3/7/2022	3/7/2022							CUST 1
Sheet1

DESTINATION SHEET AFTER UPDATE

Customer database test 2.xlsm
	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1	Part Name	Part Number	Overall Status	NCD Description	Model	LT CM Plan Due	Original LTCM Actual Due Date	Revised LTCM Actual Due Date	Close	Initial Issuance Date	Official Issuance Date	LTCM Plan Submitted	LTCM Plan Accepted Date	LTCM Plan Rejection Date	LTCM Actual Submitted	LTCM Actual Accepted Date	LTCMActualRejectionDate	NAMC
2	Part 1	11111	Closed - Cancelled	FIT CONDITION	V6				5/3/2021	4/28/2021	4/29/2021							CUST 1
3	Part 2	22222	Awaiting Official, STCM Approved	LOOSE	V6				5/3/2021	4/28/2021	4/29/2021							CUST 1
4	Part 2	11111	Officially Released, Awaiting LTCM Plan	POPPING / PULLING OUT	V6				5/3/2021	4/28/2021	4/29/2021							CUST 1
5	Part 3	33333	Officially Released, Awaiting LTCM Plan	WRONG PART	V6				3/8/2022	3/7/2022	3/7/2022							CUST 1
6	Part 3	33333	Officially Released, Awaiting LTCM Plan	SCRATCHES	I4					4/1/2022	4/2/2022							CUST 2
Sheet1

As you can see from the previous code its pulling SPECIFIC columns and you can see the complaint in row 1 became closed and cancelled and a data was added so Reitterationg the code will add the new record at the buttom but how to get it to update changed information to existing data???

 

[Kemidan2014]

Solved

Sub Update_trial_3()
  Dim srcSH As Worksheet, desSH As Worksheet
  Dim i As Long, j As Long, nRow As Long, n As Long
  Dim rng As Range, col As Range, c As Range, f As Range
  Workbooks.Open ("O:\1_All Customers\Current Complaints\ToyotaData.xlsx")
  Set srcSH = Workbooks("ToyotaData.xlsx").Sheets("Data")
  Set desSH = Workbooks("Customer Database Test 2.xlsm").Sheets("sheet1")
  Set rng = srcSH.Range("A:B,D:E,H:K,O:O,Q:AB,AH:AH")
 
  For Each c In srcSH.Range("A2", srcSH.Range("A" & Rows.count).End(3))
    Set f = desSH.Range("A:A").Find(c.Value, , xlValues, xlWhole, , , False)
    If Not f Is Nothing Then nRow = f.Row Else nRow = desSH.Range("A" & Rows.count).End(3).Row + 1
    j = 0
    For Each col In rng.Columns
      n = col.Column
      j = j + 1
      desSH.Cells(nRow, j).Value = srcSH.Cells(c.Row, n).Value
    Next
  Next
  srcSH.Parent.Close False
End Sub

Thanks Dante!