HI,
Here's what I have
1. A summary sheet with rows for each candidates. looks sth like this.

Gemma Anthony P. Male House RI 1
Lynch William J. Male House RI 1

2. 15 files containing individual worksheets for each candidate whose data needs to be aggregated. e.g. sheet 1 for Gemma, sheet 2 for Lynch.

I aim to accomplish these steps with a macron:
1. SEARCH for the individual sheet that contains candidate last name (column A in summary sheet,column A in individual sheet followed by first name )
2. ACTIVATE this individual sheet, COPY a certain range of cells (K1:K70, the same for all sheets)
3. PASTE SPECIAL these cells (select only values, transpose) to the summary sheet, at P CELL of the row of the candidate.

I appreciate your help in advance. If you think this is difficult, can you assess its level of complexity so that I could seek appropriate help?