Updating a Row Base on a selected Unique Cell Value using VBA

[ANTHONY011897]

Hi, I am new with VBA and I have a workbook that looks like this:

Sheet1 = Database
Sheet2= Search and Update

On Sheet2, I want to put a search and update button where the user can just put the unique ID of the cell from Database.

I haven't use yet any code since I can't find online.
I have attached the sample table of my database, and i want to search and update a data using case id.

 

[RudRud]

Sheet1 - Database

Book1
	A	B	C	D	E	F	G	H	I	J
1	Processed Date	Case Number	From	To	CC	CSP	Case Summary	Comments	Status	Time Status Was Change
2	45239	C231	0.0644	0.58	0.7	0.4	Ok		Pending	0.908736376
3	45088	C232	0.94	0.73	0.7	0.97	Not ok		Completed	0.764701878
4	45024	C231	0.0775	0.84	0.3	0.15	Not ok		Cancelled	0.621706851
5	45222	C231	0.8539	0.91	0.5	0.07	Ok		Cancelled	0.223434279
6	45139	C231	0.2059	0.54	0.2	0.68	Ok		Completed	0.014951483
7	45096	C232	0.8641	0.49	0.4	0.39	Ok		Completed	0.699653795
8	45141	C232	0.4448	0.37	0.3	0.88	Ok		Completed	0.174962553
9	44989	C232	0.6375	0.85	0.5	0.2	Ok		Completed	0.680569642
10	45283	C232	0.2133	0.75	0.1	0.08	Ok		Completed	0.174060058
11	45179	C232	0.0295	0.03	0.2	0.78	Ok		Completed	0.393235256
12	45177	C8989	0.6584	0.88	0.5	0.94	Ok		Completed	0.137053226
13	45048	C232	0.7109	0.39	1	0.32	Not ok		Completed	0.698482822
14	45234	C232	0.7914	0.03	1	0.61	Not ok		Pending	0.0364103
15	44962	C8989	0.6603	0.77	0.3	0.25	Not ok		Pending	0.8390586
16	44976	C231	0.2389	0.24	0.2	0.76	Not ok		Pending	0.479685147
17	45083	C8989	0.5532	0.1	0.4	0.29	Not ok		Pending	0.007211494
18	44987	C231	0.6604	0.21	0.9	0.21	Not ok		Pending	0.832374965
19	44938	C231	0.4435	0.74	0.3	0.58	Not ok		Pending	0.252941604
20	45151	C8989	0.438	0.77	0.7	0.57	Not ok		Pending	0.870089433
Sheet1

Sheet2 = Filter

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Case Number	Processed Date	From	To	CC	CSP	Case Summary	Comments	Status	Time Status Was Change		Case Number	Case Number
2	C8989	45177	0.658429	0.877805	0.494194	0.935922	Ok		Completed	0.137053226		C231	C8989
3	C8989	44962	0.660262	0.770453	0.313919	0.252828	Not ok		Pending	0.8390586		C232
4	C8989	45083	0.553202	0.10288	0.420399	0.294787	Not ok		Pending	0.007211494		C8989
5	C8989	45151	0.437964	0.773248	0.737662	0.567655	Not ok		Pending	0.870089433
Sheet2

sheet2 - view code - paste the code below

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

  If Not Intersect(Target, Range("L2:L200")) Is Nothing Then 'Case number
    Cancel = True
 
    Application.ScreenUpdating = False
    Dim sh1 As Worksheet
    Set sh1 = Sheets(1) 'Sheet 1 = Database
    Range("m2").Value = Target.Value
    Range("A2:j" & Rows.Count).ClearContents
 

     sh1.Range("A1:j" & sh1.Range("A" & Rows.Count).End(3).Row).AdvancedFilter _
    Action:=xlFilterCopy, CriteriaRange:=Range("m1:m2"), CopyToRange:=Range("A1:J1"), Unique:=False
    'A1:J = sheet 1 range, if adding new column you may change your column range since there's not dynamic yet
    'Range m1:m2 based in sheet2 case number
  Application.ScreenUpdating = True
 
  End If
End Sub

Hi, while waiting for other experts, i will share my thought here for your ref.

this one filter by using double click, you may put the case number in L2 or below and then double click with it. (unfortunately still not yet able for updating value)

Im still waiting for other reference as well since i've been using this, would like to check if got better option