Macro to copy data from multiple workbook each with multiple worksheets

[chris priyesh]

Hi Everyone,
I have 21 workbooks (i., 1.xlxs, 2.xlxs......21.xlxs) each with 8 worksheets(sheet1,sheet2....sheet8)
I am trying to merge the data from all the workbooks into 1 workbook.
The resulting workbook also contains 8 worksheets and the combined data from the workbooks for each sheet should come in the respective sheet.
(i.e., data from worksheet 1 of 1-21 workbooks should be in the worksheet 1 of the resulting file same way with sheets 2-8)
I wrote a macro but it was too lengthy (48 pages in word for half of the code)
I was hoping there could be another way around for this to be done.

FYI:
1. The main objective of this is to combine the data into one file and from there to send the data into a master file matching the data.
I am planning to do a vlookup/matchindex from the resultant sheet into the master file.

2. The code i wrote in short.
Create new workbook with 8 worksheets--save the sheet with name test--
open file 1, sheet 1-- Copy data --paste in test sheet 1
Activate file 1,sheet 2-- Copy data --paste in test sheet 2
Repeat till sheet 8 --close file 1,
Open file 2 repeat till file 21,
Sort file test sheets 1-8
Open master sheet,sheet1
Vlookup to pull the data from sheet1 of test
repeat for sheets2-8

P.s:
I tried to use the macro for merging various workbooks, but that is only working on worksheet 1 and not on the rest.

Hope you guys could help me with this.

Reg
Chris