Build a duplicate data transfer button from a table in sheet one to a table designed in sheet two and specify each person in a row

[bahrami]

Hello, if possible, please check the attachment with vba.
1- Table 1 has a list of people who have been present in the board of directors repeatedly or for several consecutive or alternate periods.
2- In sheet 2, the table is designed to transfer each person's election information in a row in front of the person's information with one button.
Thank you for your help

Example file-1400.xlsm
	A	B	C	D	E	F	G	H	I
1	Row	National Code	name	last name	Board code	Union name	Board code	Side	Election date
2	1	181-849862-6	امین	توانائی	first round	Tools Union	1	boss	1391/03/27
3	2	181-939349-6	عبدالرضا	شیرانی پور اصفهانی	first round	Tools Union	2	First Vice President	1391/03/27
4	3	175-379165-0	امیر	ابراهیم زاده	first round	Tools Union	4	Secretary	1391/03/27
5	4	181-832809-7	حمیدرضا	ابراهیمی عسکری	first round	Tools Union	5	Treasurer	1391/03/27
6	5	181-961857-9	شهرام	شوقی آزاد	first round	Tools Union	6	Inspector	1391/03/27
7	6	181-849862-6	امین	توانائی	second period	Tools Union	1	boss	1395/02/26
8	7	181-939349-6	عبدالرضا	شیرانی پور اصفهانی	second period	Tools Union	2	First Vice President	1395/02/26
9	8	175-379165-0	امیر	ابراهیم زاده	second period	Tools Union	3	Second Vice President	1395/02/26
10	9	181-961857-9	شهرام	شوقی آزاد	second period	Tools Union	4	Secretary	1395/02/26
11	10	181-832809-7	حمیدرضا	ابراهیمی عسکری	second period	Tools Union	5	Treasurer	1395/02/26
12	11	181-735988-6	علیرضا	شقایق مهر	second period	Tools Union	6	Inspector	1395/02/26
13	12	181-818343-9	حمیدرضا	ابراهیمی عسکری	Third period	Tools Union	1	boss	1399/11/06
14	13	181-939349-6	عبدالرضا	شیرانی پور اصفهانی	Third period	Tools Union	2	First Vice President	1399/11/06
15	14	175-379165-0	امیر	ابراهیم زاده	Third period	Tools Union	3	Second Vice President	1399/11/06
16	15	181-832809-7	حمیدرضا	ابراهیمی عسکری	Third period	Tools Union	4	Secretary	1399/11/06
17	16	181-961857-9	شهرام	شوقی آزاد	Third period	Tools Union	5	Treasurer	1399/11/06
18	17	174-152420-2	محمد	ابراهیم زاده	Third period	Tools Union	6	Inspector	1399/11/06
19	18	181-717714-1	فرید	فاطمی زاده	first round	Electric Trade Union	1	boss	1389/10/08
20	19	181-715665-9	وحید	فاطمی زاده	first round	Electric Trade Union	2	First Vice President	1389/10/08
21	20	181-908575-9	جمشید	رضوان	first round	Electric Trade Union	4	Secretary	1389/10/08
22	21	181-830153-9	امید	سوزنگرزاده	first round	Electric Trade Union	5	Treasurer	1389/10/08
23	22	181-746082-1	محمود	جعفری	first round	Electric Trade Union	6	Inspector	1389/10/08
24	23	181-717714-1	فرید	فاطمی زاده	second period	Electric Trade Union	1	boss	1394/10/14
25	24	181-715665-9	وحید	فاطمی زاده	second period	Electric Trade Union	2	First Vice President	1394/10/14
26	25	181-746082-1	محمود	جعفری	second period	Electric Trade Union	3	Second Vice President	1394/10/14
27	26	181-908575-9	جمشید	رضوان	second period	Electric Trade Union	4	Secretary	1394/10/14
28	27	181-830153-9	امید	سوزنگرزاده	second period	Electric Trade Union	5	Treasurer	1394/10/14
29	28	007-365682-8	وحید	آشنا	second period	Electric Trade Union	6	Inspector	1394/10/14
30	29	181-717714-1	فرید	فاطمی زاده	Third period	Electric Trade Union	1	boss	1399/10/22
31	30	181-715665-9	وحید	فاطمی زاده	Third period	Electric Trade Union	2	First Vice President	1399/10/22
32	31	181-746082-1	محمود	جعفری	Third period	Electric Trade Union	3	Second Vice President	1399/10/22
33	32	181-908575-9	جمشید	رضوان	Third period	Electric Trade Union	4	Secretary	1399/10/22
34	33	181-830153-9	امید	سوزنگرزاده	Third period	Electric Trade Union	5	Treasurer	1399/10/22
35	34	007-365682-8	وحید	آشنا	Third period	Electric Trade Union	6	Inspector	1399/10/22
36	993	181-842934-1	بهمن	حسین زاه	Fourth period	Ice Cream Guild Union	1	boss	1400/02/30
37	994	181-842934-9	اصغر	صباح	Fourth period	Ice Cream Guild Union	2	First Vice President	1400/02/30
38	995	181-842935-7	حسین	گودرزی	Fourth period	Ice Cream Guild Union	3	Second Vice President	1400/02/30
39	996	181-842936-5	محمد	کیانی	Fourth period	Ice Cream Guild Union	4	Secretary	1400/02/30
40	997	181-842937-3	احمدقلی	کارگر	Fourth period	Ice Cream Guild Union	5	Treasurer	1400/02/30
41	998	181-842938-1	سامان	کیانی	Fourth period	Ice Cream Guild Union	6	Inspector	1400/02/30
42	999	181-849862-6	امین	توانائی	Fourth period	Tools Union	1	boss	1391/03/27
43	1000	181-939349-6	عبدالرضا	شیرانی پور اصفهانی	Fourth period	Tools Union	2	First Vice President	1391/03/27
44	1001	175-379165-0	امیر	ابراهیم زاده	Fourth period	Tools Union	4	Secretary	1391/03/27
45	1002	181-832809-7	حمیدرضا	ابراهیمی عسکری	Fourth period	Tools Union	5	Treasurer	1391/03/27
46	1003	181-961857-9	شهرام	شوقی آزاد	Fourth period	Tools Union	6	Inspector	1391/03/27
47	1004	181-849862-6	امین	توانائی	Fourth period	Tools Union	1	boss	1395/02/26
Sheet1

Example file-1400.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	List of board members
2	Row	Specifications				Board code	More than two or four alternating periods								Number of company courses in the board of directors
3		Union name	name	last name	National Code		Side of the first period	Date of the first period	Side of the second period	Date of the first period	Side of the Thirdperiod	Date of the Thirdperiod	Side of the Fourth period	Date of the Fourth period
4	1	Tools Union	محمد	ابراهیمی عسکری	181-818343-9	1	0	0	0	0	boss	1399/11/06	0	0	1
5	2	Tools Union	امین	توانائی	181-849862-6	1	boss	1391/03/27	boss	1395/02/30	0	0	0	0	2
6	3	Tools Union	عبدالرضا	شیرانی پور اصفهانی	181-939349-6	2	First Vice President	1391/03/27	First Vice President	1395/02/30	First Vice President	1399/11/06	0	0	3
7	4	Tools Union	امیر	ابراهیم زاده	175-379165-0	4	Secretary	1389/08/08	Second Vice President	1394/08/17	Second Vice President	1399/11/06	0	0	3
8	5	Tools Union	حمیدرضا	ابراهیمی عسکری	181-832809-7	5	Treasurer	1389/08/08	Treasurer	1394/08/17	Secretary	1399/11/06	0	0	3
9	6	Tools Union	علیرضا	شقایق مهر	181-735988-6	6	0	1389/08/08	Inspection	1394/08/17	0	0	0	0	1
10	7	Tools Union	محمد	ابراهیم زاده	174-152420-2	6	0	0	0	0	Inspection	1399/11/06	0	0	1
11	8	Tools Union	شهرام	شوقی آزاد	181-961857-9	6	Inspector	1389/08/08	Treasurer	1394/08/17	Treasurer	1399/11/06	0	0	3
12	9	Electric Trade Union	فرید	فاطمی زاده	181-717714-1	1	boss	1389/10/08	boss	1394/10/14	boss	1399/10/22	0	0	3
13	10	Electric Trade Union	وحید	فاطمی زاده	181-715665-9	2	First Vice President	1389/10/08	First Vice President	1394/10/14	First Vice President	1399/10/22	0	0	3
14	11	Electric Trade Union	جمشید	رضوان	181-908575-9	4	Secretary	1389/10/08	Secretary	1394/10/14	Secretary	1399/10/22	0	0	3
15	12	Electric Trade Union	امید	سوزنگرزاده	181-830153-9	5	Treasurer	1389/10/08	Treasurer	1394/10/14	Treasurer	1399/10/22	0	0	3
16	13	Electric Trade Union	وحید	آشنا	007-365682-8	6	0	0	Inspection	1394/10/14	Inspection	1399/10/22	0	0	2
17	14	Electric Trade Union	محمود	جعفری	181-746082-1	6	Inspection	1389/10/08	First Vice President	1394/10/14	First Vice President	1399/10/22	0	0	3
Sheet2

 

[mumps]

Your Sheet2 contains merged cells. Please re-design it eliminating all the merged cells. You should avoid merging cells because they almost always cause problem for macros. Sheet1 also has some inconsistencies in column E with the use of "round" in some cells and "period" in other cells. In Sheet1, National Code 181-849862-6 has 2 Fourth Period board codes. Is this possible? Please review your data and make the necessary correction. The re-post your corrected data.

 

[bahrami]

Your Sheet2 contains merged cells. Please re-design it eliminating all the merged cells. You should avoid merging cells because they almost always cause problem for macros. Sheet1 also has some inconsistencies in column E with the use of "round" in some cells and "period" in other cells. In Sheet1, National Code 181-849862-6 has 2 Fourth Period board codes. Is this possible? Please review your data and make the necessary correction. The re-post your corrected data.

Thank you

 

[bahrami]

Hello, while appreciating your attention, the column includes people who have participated in the elections for several terms and the duplicate national code belongs to the same people.
In short, we want to transfer the information of the people who participated in several courses based on the national code, the name, the name of the union and the courses they participated in, and the direction and date of the elections in one row in sheet 2. thanks for your help.

Example file-1401.xlsm
	A	B	C	D	E	F	G	H	I
1	Row	National Code	name	last name	Board code	Union name	Board code	Side	Election date
2	1	181-849862-6	امین	توانائی	first round	Tools Union	1	boss	1391/03/27
3	2	181-939349-6	عبدالرضا	شیرانی پور اصفهانی	first round	Tools Union	2	First Vice President	1391/03/27
4	3	175-379165-0	امیر	ابراهیم زاده	first round	Tools Union	4	Secretary	1391/03/27
5	4	181-832809-7	حمیدرضا	ابراهیمی عسکری	first round	Tools Union	5	Treasurer	1391/03/27
6	5	181-961857-9	شهرام	شوقی آزاد	first round	Tools Union	6	Inspector	1391/03/27
7	6	181-849862-6	امین	توانائی	second period	Tools Union	1	boss	1395/02/26
8	7	181-939349-6	عبدالرضا	شیرانی پور اصفهانی	second period	Tools Union	2	First Vice President	1395/02/26
9	8	175-379165-0	امیر	ابراهیم زاده	second period	Tools Union	3	Second Vice President	1395/02/26
10	9	181-961857-9	شهرام	شوقی آزاد	second period	Tools Union	4	Secretary	1395/02/26
11	10	181-832809-7	حمیدرضا	ابراهیمی عسکری	second period	Tools Union	5	Treasurer	1395/02/26
12	11	181-735988-6	علیرضا	شقایق مهر	second period	Tools Union	6	Inspector	1395/02/26
13	12	181-818343-9	حمیدرضا	ابراهیمی عسکری	Third period	Tools Union	1	boss	1399/11/06
14	13	181-939349-6	عبدالرضا	شیرانی پور اصفهانی	Third period	Tools Union	2	First Vice President	1399/11/06
15	14	175-379165-0	امیر	ابراهیم زاده	Third period	Tools Union	3	Second Vice President	1399/11/06
16	15	181-832809-7	حمیدرضا	ابراهیمی عسکری	Third period	Tools Union	4	Secretary	1399/11/06
17	16	181-961857-9	شهرام	شوقی آزاد	Third period	Tools Union	5	Treasurer	1399/11/06
18	17	174-152420-2	محمد	ابراهیم زاده	Third period	Tools Union	6	Inspector	1399/11/06
19	18	181-717714-1	فرید	فاطمی زاده	first round	Electric Trade Union	1	boss	1389/10/08
20	19	181-715665-9	وحید	فاطمی زاده	first round	Electric Trade Union	2	First Vice President	1389/10/08
21	20	181-908575-9	جمشید	رضوان	first round	Electric Trade Union	4	Secretary	1389/10/08
22	21	181-830153-9	امید	سوزنگرزاده	first round	Electric Trade Union	5	Treasurer	1389/10/08
23	22	181-746082-1	محمود	جعفری	first round	Electric Trade Union	6	Inspector	1389/10/08
24	23	181-717714-1	فرید	فاطمی زاده	second period	Electric Trade Union	1	boss	1394/10/14
25	24	181-715665-9	وحید	فاطمی زاده	second period	Electric Trade Union	2	First Vice President	1394/10/14
26	25	181-746082-1	محمود	جعفری	second period	Electric Trade Union	3	Second Vice President	1394/10/14
27	26	181-908575-9	جمشید	رضوان	second period	Electric Trade Union	4	Secretary	1394/10/14
28	27	181-830153-9	امید	سوزنگرزاده	second period	Electric Trade Union	5	Treasurer	1394/10/14
29	28	007-365682-8	وحید	آشنا	second period	Electric Trade Union	6	Inspector	1394/10/14
30	29	181-717714-1	فرید	فاطمی زاده	Third period	Electric Trade Union	1	boss	1399/10/22
31	30	181-715665-9	وحید	فاطمی زاده	Third period	Electric Trade Union	2	First Vice President	1399/10/22
32	31	181-746082-1	محمود	جعفری	Third period	Electric Trade Union	3	Second Vice President	1399/10/22
33	32	181-908575-9	جمشید	رضوان	Third period	Electric Trade Union	4	Secretary	1399/10/22
34	33	181-830153-9	امید	سوزنگرزاده	Third period	Electric Trade Union	5	Treasurer	1399/10/22
35	34	007-365682-8	وحید	آشنا	Third period	Electric Trade Union	6	Inspector	1399/10/22
36	999	181-849862-6	امین	توانائی	Fourth period	Tools Union	1	boss	1391/03/27
37	1000	181-939349-6	عبدالرضا	شیرانی پور اصفهانی	Fourth period	Tools Union	2	First Vice President	1391/03/27
38	1001	175-379165-0	امیر	ابراهیم زاده	Fourth period	Tools Union	4	Secretary	1391/03/27
39	1002	181-832809-7	حمیدرضا	ابراهیمی عسکری	Fourth period	Tools Union	5	Treasurer	1391/03/27
40	1003	181-961857-9	شهرام	شوقی آزاد	Fourth period	Tools Union	6	Inspector	1391/03/27
41	1004	181-849862-6	امین	توانائی	Fourth period	Tools Union	1	boss	1395/02/26
42	993	181-842934-1	بهمن	حسین زاه	Fourth period	Ice Cream Guild Union	1	boss	1400/02/30
43	994	181-842934-9	اصغر	صباح	Fourth period	Ice Cream Guild Union	2	First Vice President	1400/02/30
44	995	181-842935-7	حسین	گودرزی	Fourth period	Ice Cream Guild Union	3	Second Vice President	1400/02/30
45	996	181-842936-5	محمد	کیانی	Fourth period	Ice Cream Guild Union	4	Secretary	1400/02/30
46	997	181-842937-3	احمدقلی	کارگر	Fourth period	Ice Cream Guild Union	5	Treasurer	1400/02/30
47	998	181-842938-1	سامان	کیانی	Fourth period	Ice Cream Guild Union	6	Inspector	1400/02/30
Sheet1

Example file-1401.xlsm
	E	F	G	H	I	J	K	L	M	N	O
1	National Code	Board code	Side of the first period	Date of the first period	Side of the second period	Date of the first period	Side of the Thirdperiod	Date of the Thirdperiod	Side of the Fourth period	Date of the Fourth period	Number of company courses in the board of directors
2	181-818343-9	1	0	0	0	0	boss	1399/11/06	0	0	1
3	181-849862-6	1	boss	1391/03/27	boss	1395/02/30	0	0	0	0	2
4	181-939349-6	2	First Vice President	1391/03/27	First Vice President	1395/02/30	First Vice President	1399/11/06	0	0	3
5	175-379165-0	4	Secretary	1389/08/08	Second Vice President	1394/08/17	Second Vice President	1399/11/06	0	0	3
6	181-832809-7	5	Treasurer	1389/08/08	Treasurer	1394/08/17	Secretary	1399/11/06	0	0	3
7	181-735988-6	6	0	1389/08/08	Inspection	1394/08/17	0	0	0	0	1
8	174-152420-2	6	0	0	0	0	Inspection	1399/11/06	0	0	1
9	181-961857-9	6	Inspector	1389/08/08	Treasurer	1394/08/17	Treasurer	1399/11/06	0	0	3
10	181-717714-1	1	boss	1389/10/08	boss	1394/10/14	boss	1399/10/22	0	0	3
11	181-715665-9	2	First Vice President	1389/10/08	First Vice President	1394/10/14	First Vice President	1399/10/22	0	0	3
12	181-908575-9	4	Secretary	1389/10/08	Secretary	1394/10/14	Secretary	1399/10/22	0	0	3
13	181-830153-9	5	Treasurer	1389/10/08	Treasurer	1394/10/14	Treasurer	1399/10/22	0	0	3
14	007-365682-8	6	0	0	Inspection	1394/10/14	Inspection	1399/10/22	0	0	2
15	181-746082-1	6	Inspection	1389/10/08	First Vice President	1394/10/14	First Vice President	1399/10/22	0	0	3
Sheet2

 

[mumps]

In the Report sheet, place the headers starting with National Code in cell A1 not E1. Then in F1, correct "Date of the first period" to "Date of the second period". In Sheet1, some of the Board Codes in column E have leading and/or trailing spaces. Remove all the trailing spaces in the codes in column E. Then try this macro:

Sub TransferData()
    Application.ScreenUpdating = False
    Dim lastRow As Long, lRow As Long, srcWS As Worksheet, desWS As Worksheet, v As Variant
    Dim i As Long, rCount As Long, fnd As Range, rng As Range, x As Long
    Set srcWS = Sheets("Sheet1")
    Set desWS = Sheets("Report")
    lastRow = srcWS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    v = srcWS.Range("A1").CurrentRegion.Value
    Set dic = CreateObject("Scripting.Dictionary")
    For i = 2 To UBound(v, 1)
        If Not dic.Exists(v(i, 2)) Then
            dic.Add v(i, 2), Nothing
            With srcWS
                .Range("A1").CurrentRegion.AutoFilter 2, v(i, 2)
                rCount = .[subtotal(103,A:A)] - 1
                With desWS
                    lRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
                    Set fnd = .Rows(1).Find(Split(v(i, 5), " ")(0), LookIn:=xlValues, lookat:=xlPart)
                    .Range("A" & lRow + 1).Resize(, 2).Value = Array(v(i, 2), v(i, 7))
                    For Each rng In srcWS.Range("H2:H" & lastRow).SpecialCells(xlCellTypeVisible)
                        .Cells(lRow + 1, fnd.Column + x).Resize(, 2).Value = Array(rng, rng.Offset(, 1))
                        x = x + 2
                    Next rng
                    .Range("K" & lRow + 1) = rCount
                    x = 0
                End With
            End With
        End If
    Next i
    srcWS.Range("A1").AutoFilter
    Application.ScreenUpdating = True
End Sub

 

[bahrami]

In the Report sheet, place the headers starting with National Code in cell A1 not E1. Then in F1, correct "Date of the first period" to "Date of the second period". In Sheet1, some of the Board Codes in column E have leading and/or trailing spaces. Remove all the trailing spaces in the codes in column E. Then try this macro:

Sub TransferData()
    Application.ScreenUpdating = False
    Dim lastRow As Long, lRow As Long, srcWS As Worksheet, desWS As Worksheet, v As Variant
    Dim i As Long, rCount As Long, fnd As Range, rng As Range, x As Long
    Set srcWS = Sheets("Sheet1")
    Set desWS = Sheets("Report")
    lastRow = srcWS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    v = srcWS.Range("A1").CurrentRegion.Value
    Set dic = CreateObject("Scripting.Dictionary")
    For i = 2 To UBound(v, 1)
        If Not dic.Exists(v(i, 2)) Then
            dic.Add v(i, 2), Nothing
            With srcWS
                .Range("A1").CurrentRegion.AutoFilter 2, v(i, 2)
                rCount = .[subtotal(103,A:A)] - 1
                With desWS
                    lRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
                    Set fnd = .Rows(1).Find(Split(v(i, 5), " ")(0), LookIn:=xlValues, lookat:=xlPart)
                    .Range("A" & lRow + 1).Resize(, 2).Value = Array(v(i, 2), v(i, 7))
                    For Each rng In srcWS.Range("H2:H" & lastRow).SpecialCells(xlCellTypeVisible)
                        .Cells(lRow + 1, fnd.Column + x).Resize(, 2).Value = Array(rng, rng.Offset(, 1))
                        x = x + 2
                    Next rng
                    .Range("K" & lRow + 1) = rCount
                    x = 0
                End With
            End With
        End If
    Next i
    srcWS.Range("A1").AutoFilter
    Application.ScreenUpdating = True
End Sub

Thank you very much

 

[mumps]

You are very welcome.