VBA Stock movement summary

[jimmy1986]

Hello all,
I have a file for the stock movement summary.
On sheet 1 I have a table (name: Summary) to check stock in and out.
I use Xlooup to find the info from the table (name: ID) in sheet 2 and count the stock in and out from sheet 3 (table name IN) and 4 (table name Out).
Please help me some codes VBA.
-To transfer the formula in every column in the table (summary) to VBA
The formula is :
-=XLOOKUP([@Name],Table2[Name],Table2[PL]) ' column PL
-=XLOOKUP([@Name],Table2[Name],Table2[HE]) 'column HE
-=[@Name]&[@Color] 'column Name and color
-=SUMIFS(IN[IN],IN[Name and Color],[@[Name and Color]],IN[DATE],">="&$L$2,IN[DATE],"<="&$L$3) 'Column IN
-=SUMIFS(OUT[OUT],OUT[Name and Color],[@[Name and Color]],IN[DATE],">="&$L$2,IN[DATE],"<="&$L$3) 'Column OUT
-=[@IN]-[@OUT] 'Column Remain
Then make it appear as value.
So every time I change the name and color or delete the row, the formula still works and keeps the value appear.
I am really appreciated for any help.

TEST.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1
2											From Date	1/6/21
3											To Date	30/6/21
4
5
6		No	Name	Color	PL	HE	DM	CB	MAX	TB	Name and Color	IN	OUT	Remain
7		1	Name1	Blue	HT	C	D	30	1.5	1.5	Name1Blue	1522	423	1099
8		2	Name1	Green	HT	C	D	30	1.5	1.5	Name1Green	4245	534	3711
9		3	Name2	Red	HT	C	D	30	1.5	1.5	Name2Red	54345	534	53811
10		4	Name2	Pink	HT	C	D	30	1.5	1.5	Name2Pink	4234	34	4200
11		5	Name3	Blue	HT	T	M	20	2	3	Name3Blue	4234	65	4169
12		6	Name3	Green	HT	T	M	20	2	3	Name3Green	53453	76	53377
13		7	Name4	Red	HT	T	M	20	2	3	Name4Red	4564	65	4499
14		8	Name4	Pink	HT	T	M	20	2	3	Name4Pink	3453	76	3377
15
16
SUMMARY

Cell Formulas
Range	Formula
E7:E14	E7	=XLOOKUP([@Name],Table2[Name],Table2[PL])
F7:F14	F7	=XLOOKUP([@Name],Table2[Name],Table2[HE])
G7:G14	G7	=XLOOKUP([@Name],Table2[Name],Table2[DM])
H7:H14	H7	=XLOOKUP([@Name],Table2[Name],Table2[CB])
I7:I14	I7	=XLOOKUP([@HE],Table2[HE],Table2[MAX])
J7:J14	J7	=XLOOKUP([@DM],Table2[DM],Table2[TB])
K7:K14	K7	=[@Name]&[@Color]
L7:L14	L7	=SUMIFS(IN[IN],IN[Name and Color],[@[Name and Color]],IN[DATE],">="&$L$2,IN[DATE],"<="&$L$3)
M7:M14	M7	=SUMIFS(OUT[OUT],OUT[Name and Color],[@[Name and Color]],IN[DATE],">="&$L$2,IN[DATE],"<="&$L$3)
N7:N14	N7	=[@IN]-[@OUT]

TEST.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1
2
3					ID
4
5
6			Name	Color	PL	HE	DM	CB	MAX	TB	Name and Color
7			Name1		HT	C	D	30	1.5	1.5
8			Name2		HT	C	D	30	1.5	1.5
9			Name3		HT	T	M	20	2	3
10			Name4		HT	T	M	20	2	3
11
12
13
14
ID

TEST.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N
2
3
4					STOCK IN
5
6		DATE	Name	Color	PL	He	DM	CB	MAX	TB	Name and Color	IN
7		1/6/21	Name1	Blue	HT	C	D	30	1.5	1.5	Name1Blue	1522
8		2/6/21	Name1	Green	HT	C	D	30	1.5	2	Name1Green	4245
9		3/6/21	Name2	Red	HT	C	D	30	1.5	1.5	Name2Red	54345
10		4/6/21	Name2	Pink	HT	C	D	30	1.5	1	Name2Pink	4234
11		5/6/21	Name3	Blue	HT	C	D	30	1.5	1.5	Name3Blue	4234
12		6/6/21	Name3	Green	HT	C	D	30	1.5	2	Name3Green	53453
13		7/6/21	Name4	Red	HT	C	D	30	1.5	1.5	Name4Red	4564
14		8/6/21	Name4	Pink	HT	C	D	30	1.5	1	Name4Pink	3453
15
16
IN

Cell Formulas
Range	Formula
L7:L14	L7	=[@Name]&[@Color]

TEST.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N
2
3					STOCK OUT
4
5
6		DATE	Name	Color	PL	He	DM	CB	MAX	TB	Name and Color	OUT
7		1/6/21	Name1	Blue	HT	C	D	30	1.5	1.5	Name1Blue	423
8		2/6/21	Name1	Green	HT	C	D	30	1.5	2	Name1Green	534
9		3/6/21	Name2	Red	HT	C	D	30	1.5	1.5	Name2Red	534
10		4/6/21	Name2	Pink	HT	C	D	30	1.5	1	Name2Pink	34
11		5/6/21	Name3	Blue	HT	C	D	30	1.5	1.5	Name3Blue	65
12		6/6/21	Name3	Green	HT	C	D	30	1.5	2	Name3Green	76
13		7/6/21	Name4	Red	HT	C	D	30	1.5	1.5	Name4Red	65
14		8/6/21	Name4	Pink	HT	C	D	30	1.5	1	Name4Pink	76
15
16
17
18
OUT

Cell Formulas
Range	Formula
L7:L14	L7	=[@Name]&[@Color]

 

[jimmy1986]

I try this:

Option Explicit
Sub table()
Dim table As ListObject
Set table = ActiveSheet.ListObjects(1)
table.ListColumns("PL").DataBodyRange.Formula = "=XLOOKUP([@Name],Table2[Name],Table2[PL])"
End Sub

It works but I do not know how to paste value and I want every time I change the Data (such as : name, Color... ) the column auto update the value, not by clicking on Sub button.