Using ADODB way t populate data under header from file to another

[abdo meghari]

Hello,
this code will populate data under headers when match with the other file .
but I have TOTAL row and what I want to insert new row with the same formatting and borders before TOTAL row and expand range for formula in column E for last row if the rows numbers in source file bigger than destination file and if the rows numbers in source file smaller than destination file then should delete empty rows before TOTAL row in destination file

FILE SOURCE

TIRES.xlsx
	A	B	C	D	E	F
1	ITEM	CODE	BRAND	UNIT PRICE	QTY	TOTAL
2	1	1568	MOTORMECH 150A L KOR	1,000.000	2	2,000.000
3	2	1586	NOVA 200A L KOR	1,500.000	2	3,000.000
4	3	1540	VEGA 55A L KOR	400.000	1	400.000
5	4	1300	XPRO 70A L KOR	540.000	4	2,160.000
6	5	1334	XPRO 70A R KOR	540.000	4	2,160.000
7	6	1588	BATTREY 30A CHI	275.000	1	275.000
8	7	1590	BOHANO 55A L JAP	370.000	1	370.000
9	8	1343	KM 215/65R16 TA21 KOR	435.000	5	2,175.000
10	9	1518	KM 195/65R15 TA21 KOR	350.000	4	1,400.000
11	10	1335	XPRO 150A L KOR	1,000.000	2	2,000.000
12	11	1496	XPRO 100A L KOR واطية	625.000	1	625.000
13	12	1448	DUNLOP 285/60R18 AT25 JAP	1,110.000	1	1,110.000
14	13	1594	APPLLO 265/65R17 INDIA	595.000	4	2,380.000
15	14	1471	BS 1400R20 R180 JAP	4,500.000	2	9,000.000
16	15	1495	XPRO 200A L KOR	1,285.000	4	5,140.000
17	16	1335	XPRO 150A L KOR	1,000.000	4	4,000.000
18	17	1595	XPRO 100A R KOR	610.000	10	6,100.000
19	18	1341	XPRO 100A L KOR	620.000	10	6,200.000
20	19	1334	XPRO 70A R KOR	510.000	15	7,650.000
21	20	1300	XPRO 70A L KOR	515.000	10	5,150.000
22	21	1338	XPRO 60A L KOR	425.000	15	6,375.000
23	22	1337	XPRO 55A L KOR	390.000	10	3,900.000
24	23	1596	YOKOHAMA 7A MALAYSIA	265.000	1	265.000
25	24	1598	XPRO 80A L KPOR	570.000	1	570.000
26	25	1599	HANKOOK 215/60R17 KOR	525.000	4	2,100.000
27	26	1603	335/80R20 CHI	3,125.000	4	12,500.000
28	27	1334	XPRO 70A R KOR	540.000	5	2,700.000
29	28	1432	ASIMCO 70A R KOR	540.000	9	4,860.000
30	29	1606	FORTEN 235/60R18 XL THI	445.000	4	1,780.000
31	30	1607	TOP TRUST 6.00-16 8PR F2-1 TT CHI	365.000	2	730.000
32	31	1608	PROLITE 74A R KOR	565.000	1	565.000
33	32	1609	HANKOOK 235/60R17 K406 KOR	540.000	5	2,700.000
34	33	1611	KM 155R13C KC55 KOR	415.000	4	1,660.000
35	34	1612	KM 195R15C KC53 VIT	440.000	2	880.000
36	35	1613	CRYSTAL 150A KOR	1,000.000	10	10,000.000
37	36	1335	XPRO 150A L KOR	1,000.000	2	2,000.000
38	37	1300	XPRO 70A L KOR	505.000	2	1,010.000
39	38	1541	VEGA 70A L KOR	505.000	10	5,050.000
40	39	1448	DUNLOP 285/60R18 AT25 JAP	1,125.000	5	5,625.000
41	40	1614	PRINX 235/70R16 HH2 THI	475.000	4	1,900.000
42	41	1534	BS 265/65R17 D693 THI	895.000	4	3,580.000
43	42	1462	HANKOOK 215/70R16C KOR	490.000	4	1,960.000
44	43	1334	XPRO 70A R KOR	515.000	2	1,030.000
45	44	1447	PROLITE 44A L KOR	365.000	1	365.000
46	45	1617	XPRO 74A L KOR	515.000	1	515.000
47	46	1300	XPRO 70A L KOR	505.000	1	505.000
48	47	1339	XPRO 90A L KOR	600.000	1	600.000
49	48	1620	YOKOHAMA 205/65R16 ES32 JAP	455.000	4	1,820.000
50	49	1624	DUNLOP 265/70R18 AT23 JAP	1,100.000	4	4,400.000
51	50	1621	LASSA 4*4 235/70R16 A/T2 TR	575.000	4	2,300.000
52	51	1623	LASSA 235/50R19 H/P2 TR	560.000	4	2,240.000
53	52	1567	DUNLOP 285/65R17 JAP	950.000	2	1,900.000
54	53	1619	VEGA 70A R KOR	500.000	5	2,500.000
55	54	1339	XPRO 90A L KOR	605.000	9	5,445.000
56	55	1598	XPRO 80A L KPOR	570.000	1	570.000
57	56	1626	KM 285/75R16 MT51 VIT	835.000	8	6,680.000
58	57	1338	XPRO 60A L KOR	425.000	2	850.000
Page 0

Cell Formulas
Range	Formula
F2:F58	F2	=D2*E2

destination file

RESULT from AODB.xlsm
	A	B	C	D	E
23	ITEM	BRAND	QTY	UNIT PRICE	TOTAL
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69	TOTAL				0.00
SH1

Cell Formulas
Range	Formula
E69	E69	=SUM(E24:E68)

result in destination file when add new rows before TOTAL row.

RESULT from AODB.xlsm
	A	B	C	D	E
23	ITEM	BRAND	QTY	UNIT PRICE	TOTAL
24	1	MOTORMECH 150A L KOR	2	1,000.00	2,000.00
25	2	NOVA 200A L KOR	2	1,500.00	3,000.00
26	3	VEGA 55A L KOR	1	400.00	400.00
27	4	XPRO 70A L KOR	4	540.00	2,160.00
28	5	XPRO 70A R KOR	4	540.00	2,160.00
29	6	BATTREY 30A CHI	1	275.00	275.00
30	7	BOHANO 55A L JAP	1	370.00	370.00
31	8	KM 215/65R16 TA21 KOR	5	435.00	2,175.00
32	9	KM 195/65R15 TA21 KOR	4	350.00	1,400.00
33	10	XPRO 150A L KOR	2	1,000.00	2,000.00
34	11	XPRO 100A L KOR واطية	1	625.00	625.00
35	12	DUNLOP 285/60R18 AT25 JAP	1	1,110.00	1,110.00
36	13	APPLLO 265/65R17 INDIA	4	595.00	2,380.00
37	14	BS 1400R20 R180 JAP	2	4,500.00	9,000.00
38	15	XPRO 200A L KOR	4	1,285.00	5,140.00
39	16	XPRO 150A L KOR	4	1,000.00	4,000.00
40	17	XPRO 100A R KOR	10	610.00	6,100.00
41	18	XPRO 100A L KOR	10	620.00	6,200.00
42	19	XPRO 70A R KOR	15	510.00	7,650.00
43	20	XPRO 70A L KOR	10	515.00	5,150.00
44	21	XPRO 60A L KOR	15	425.00	6,375.00
45	22	XPRO 55A L KOR	10	390.00	3,900.00
46	23	YOKOHAMA 7A MALAYSIA	1	265.00	265.00
47	24	XPRO 80A L KPOR	1	570.00	570.00
48	25	HANKOOK 215/60R17 KOR	4	525.00	2,100.00
49	26	335/80R20 CHI	4	3,125.00	12,500.00
50	27	XPRO 70A R KOR	5	540.00	2,700.00
51	28	ASIMCO 70A R KOR	9	540.00	4,860.00
52	29	FORTEN 235/60R18 XL THI	4	445.00	1,780.00
53	30	TOP TRUST 6.00-16 8PR F2-1 TT CHI	2	365.00	730.00
54	31	PROLITE 74A R KOR	1	565.00	565.00
55	32	HANKOOK 235/60R17 K406 KOR	5	540.00	2,700.00
56	33	KM 155R13C KC55 KOR	4	415.00	1,660.00
57	34	KM 195R15C KC53 VIT	2	440.00	880.00
58	35	CRYSTAL 150A KOR	10	1,000.00	10,000.00
59	36	XPRO 150A L KOR	2	1,000.00	2,000.00
60	37	XPRO 70A L KOR	2	505.00	1,010.00
61	38	VEGA 70A L KOR	10	505.00	5,050.00
62	39	DUNLOP 285/60R18 AT25 JAP	5	1,125.00	5,625.00
63	40	PRINX 235/70R16 HH2 THI	4	475.00	1,900.00
64	41	BS 265/65R17 D693 THI	4	895.00	3,580.00
65	42	HANKOOK 215/70R16C KOR	4	490.00	1,960.00
66	43	XPRO 70A R KOR	2	515.00	1,030.00
67	44	PROLITE 44A L KOR	1	365.00	365.00
68	45	XPRO 74A L KOR	1	515.00	515.00
69	46	XPRO 70A L KOR	1	505.00	505.00
70	47	XPRO 90A L KOR	1	600.00	600.00
71	48	YOKOHAMA 205/65R16 ES32 JAP	4	455.00	1,820.00
72	49	DUNLOP 265/70R18 AT23 JAP	4	1,100.00	4,400.00
73	50	LASSA 4*4 235/70R16 A/T2 TR	4	575.00	2,300.00
74	51	LASSA 235/50R19 H/P2 TR	4	560.00	2,240.00
75	52	DUNLOP 285/65R17 JAP	2	950.00	1,900.00
76	53	VEGA 70A R KOR	5	500.00	2,500.00
77	54	XPRO 90A L KOR	9	605.00	5,445.00
78	55	XPRO 80A L KPOR	1	570.00	570.00
79	56	KM 285/75R16 MT51 VIT	8	835.00	6,680.00
80	57	XPRO 60A L KOR	2	425.00	850.00
81	TOTAL				166,875.00
SH1

Cell Formulas
Range	Formula
E81	E81	=SUM(E24:E79)

another example

TIRES.xlsx
	A	B	C	D	E	F
1	ITEM	CODE	BRAND	UNIT PRICE	QTY	TOTAL
2	1	1568	MOTORMECH 150A L KOR	1,000.000	2	2,000.000
3	2	1586	NOVA 200A L KOR	1,500.000	2	3,000.000
4	3	1540	VEGA 55A L KOR	400.000	1	400.000
5	4	1300	XPRO 70A L KOR	540.000	4	2,160.000
6	5	1334	XPRO 70A R KOR	540.000	4	2,160.000
7	6	1588	BATTREY 30A CHI	275.000	1	275.000
8	7	1590	BOHANO 55A L JAP	370.000	1	370.000
9	8	1343	KM 215/65R16 TA21 KOR	435.000	5	2,175.000
10	9	1518	KM 195/65R15 TA21 KOR	350.000	4	1,400.000
11	10	1335	XPRO 150A L KOR	1,000.000	2	2,000.000
12	11	1496	XPRO 100A L KOR واطية	625.000	1	625.000
13	12	1448	DUNLOP 285/60R18 AT25 JAP	1,110.000	1	1,110.000
14	13	1594	APPLLO 265/65R17 INDIA	595.000	4	2,380.000
15	14	1471	BS 1400R20 R180 JAP	4,500.000	2	9,000.000
16	15	1495	XPRO 200A L KOR	1,285.000	4	5,140.000
17	16	1335	XPRO 150A L KOR	1,000.000	4	4,000.000
18	17	1595	XPRO 100A R KOR	610.000	10	6,100.000
19	18	1341	XPRO 100A L KOR	620.000	10	6,200.000
20	19	1334	XPRO 70A R KOR	510.000	15	7,650.000
21	20	1300	XPRO 70A L KOR	515.000	10	5,150.000
22	21	1338	XPRO 60A L KOR	425.000	15	6,375.000
23	22	1337	XPRO 55A L KOR	390.000	10	3,900.000
24	23	1596	YOKOHAMA 7A MALAYSIA	265.000	1	265.000
25	24	1598	XPRO 80A L KPOR	570.000	1	570.000
26	25	1599	HANKOOK 215/60R17 KOR	525.000	4	2,100.000
27	26	1603	335/80R20 CHI	3,125.000	4	12,500.000
28	27	1334	XPRO 70A R KOR	540.000	5	2,700.000
29	28	1432	ASIMCO 70A R KOR	540.000	9	4,860.000
30	29	1606	FORTEN 235/60R18 XL THI	445.000	4	1,780.000
31	30	1607	TOP TRUST 6.00-16 8PR F2-1 TT CHI	365.000	2	730.000
32	31	1608	PROLITE 74A R KOR	565.000	1	565.000
33	32	1609	HANKOOK 235/60R17 K406 KOR	540.000	5	2,700.000
34	33	1611	KM 155R13C KC55 KOR	415.000	4	1,660.000
35	34	1612	KM 195R15C KC53 VIT	440.000	2	880.000
36	35	1613	CRYSTAL 150A KOR	1,000.000	10	10,000.000
37	36	1335	XPRO 150A L KOR	1,000.000	2	2,000.000
38	37	1300	XPRO 70A L KOR	505.000	2	1,010.000
39	38	1541	VEGA 70A L KOR	505.000	10	5,050.000
40	39	1448	DUNLOP 285/60R18 AT25 JAP	1,125.000	5	5,625.000
41	40	1614	PRINX 235/70R16 HH2 THI	475.000	4	1,900.000
42	41	1534	BS 265/65R17 D693 THI	895.000	4	3,580.000
43	42	1462	HANKOOK 215/70R16C KOR	490.000	4	1,960.000
44	43	1334	XPRO 70A R KOR	515.000	2	1,030.000
45	44	1447	PROLITE 44A L KOR	365.000	1	365.000
46	45	1617	XPRO 74A L KOR	515.000	1	515.000
47	46	1300	XPRO 70A L KOR	505.000	1	505.000
48	47	1339	XPRO 90A L KOR	600.000	1	600.000
49	48	1620	YOKOHAMA 205/65R16 ES32 JAP	455.000	4	1,820.000
50	49	1624	DUNLOP 265/70R18 AT23 JAP	1,100.000	4	4,400.000
Page 0

Cell Formulas
Range	Formula
F2:F50	F2	=D2*E2

destination file

RESULT from AODB.xlsm
	A	B	C	D	E
23	ITEM	BRAND	QTY	UNIT PRICE	TOTAL
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81	TOTAL				0.00
SH1

Cell Formulas
Range	Formula
E81	E81	=SUM(E24:E79)

result when delete empty rows

RESULT from AODB.xlsm
	A	B	C	D	E
23	ITEM	BRAND	QTY	UNIT PRICE	TOTAL
24	1	MOTORMECH 150A L KOR	2	1,000.00	2,000.00
25	2	NOVA 200A L KOR	2	1,500.00	3,000.00
26	3	VEGA 55A L KOR	1	400.00	400.00
27	4	XPRO 70A L KOR	4	540.00	2,160.00
28	5	XPRO 70A R KOR	4	540.00	2,160.00
29	6	BATTREY 30A CHI	1	275.00	275.00
30	7	BOHANO 55A L JAP	1	370.00	370.00
31	8	KM 215/65R16 TA21 KOR	5	435.00	2,175.00
32	9	KM 195/65R15 TA21 KOR	4	350.00	1,400.00
33	10	XPRO 150A L KOR	2	1,000.00	2,000.00
34	11	XPRO 100A L KOR واطية	1	625.00	625.00
35	12	DUNLOP 285/60R18 AT25 JAP	1	1,110.00	1,110.00
36	13	APPLLO 265/65R17 INDIA	4	595.00	2,380.00
37	14	BS 1400R20 R180 JAP	2	4,500.00	9,000.00
38	15	XPRO 200A L KOR	4	1,285.00	5,140.00
39	16	XPRO 150A L KOR	4	1,000.00	4,000.00
40	17	XPRO 100A R KOR	10	610.00	6,100.00
41	18	XPRO 100A L KOR	10	620.00	6,200.00
42	19	XPRO 70A R KOR	15	510.00	7,650.00
43	20	XPRO 70A L KOR	10	515.00	5,150.00
44	21	XPRO 60A L KOR	15	425.00	6,375.00
45	22	XPRO 55A L KOR	10	390.00	3,900.00
46	23	YOKOHAMA 7A MALAYSIA	1	265.00	265.00
47	24	XPRO 80A L KPOR	1	570.00	570.00
48	25	HANKOOK 215/60R17 KOR	4	525.00	2,100.00
49	26	335/80R20 CHI	4	3,125.00	12,500.00
50	27	XPRO 70A R KOR	5	540.00	2,700.00
51	28	ASIMCO 70A R KOR	9	540.00	4,860.00
52	29	FORTEN 235/60R18 XL THI	4	445.00	1,780.00
53	30	TOP TRUST 6.00-16 8PR F2-1 TT CHI	2	365.00	730.00
54	31	PROLITE 74A R KOR	1	565.00	565.00
55	32	HANKOOK 235/60R17 K406 KOR	5	540.00	2,700.00
56	33	KM 155R13C KC55 KOR	4	415.00	1,660.00
57	34	KM 195R15C KC53 VIT	2	440.00	880.00
58	35	CRYSTAL 150A KOR	10	1,000.00	10,000.00
59	36	XPRO 150A L KOR	2	1,000.00	2,000.00
60	37	XPRO 70A L KOR	2	505.00	1,010.00
61	38	VEGA 70A L KOR	10	505.00	5,050.00
62	39	DUNLOP 285/60R18 AT25 JAP	5	1,125.00	5,625.00
63	40	PRINX 235/70R16 HH2 THI	4	475.00	1,900.00
64	41	BS 265/65R17 D693 THI	4	895.00	3,580.00
65	42	HANKOOK 215/70R16C KOR	4	490.00	1,960.00
66	43	XPRO 70A R KOR	2	515.00	1,030.00
67	44	PROLITE 44A L KOR	1	365.00	365.00
68	45	XPRO 74A L KOR	1	515.00	515.00
69	46	XPRO 70A L KOR	1	505.00	505.00
70	47	XPRO 90A L KOR	1	600.00	600.00
71	48	YOKOHAMA 205/65R16 ES32 JAP	4	455.00	1,820.00
72	49	DUNLOP 265/70R18 AT23 JAP	4	1,100.00	4,400.00
73	TOTAL				145,240.00
SH1

Cell Formulas
Range	Formula
E73	E73	=SUM(E24:E72)

the code

Sub test()
    Dim myDir As String, cn As Object, rs As Object, r, myHeading
    With Application.FileDialog(msoFileDialogFolderPicker)
        .Title = "Select Folder"
        If .Show Then myDir = .SelectedItems(1) & "\"
    End With
    If myDir = "" Then Exit Sub
    Application.ScreenUpdating = False
    Set cn = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")
   
    With ThisWorkbook
        With .Sheets(1).Cells(23, 1).CurrentRegion
            myHeading = "`" & Join(.Parent.Evaluate(.Rows(1).Address & "&"""""), "`, `") & "`"
          
            If .Rows.Count > 1 Then .Offset(1).Resize(.Rows.Count - 1).ClearContents
        End With
        With cn
            .Provider = "Microsoft.Ace.OLEDB.12.0"
            .Properties("Extended Properties") = "Excel 12.0;HDR=Yes;"
            .Open myDir & "TIRES.xlsx"
        End With
        rs.Open "Select " & myHeading & " From TiresTable", cn
       
        .Sheets(1).Cells(24, 1).CopyFromRecordset rs
       
    End With
    Set cn = Nothing: Set rs = Nothing
    Application.ScreenUpdating = True
End Sub

last thing I would running the macro without select folder and show dialog . two files are existed in the same folder(C:\Users\abdom\Desktop\RT\.
I know this code is not easy if there is alternative I accept that.