If you want some help, you need to explain your requirements.
Also it is customary to ask for assistance - everyone who responds is a volunteer!
1. You say you want to merge data.
Does this mean you want to concatenate text strings, e.g., Cell C1 on Sheet1 in workbook One.xls contains "My" and Cell C1 on Sheet1 in workbook Two.xls contains "Document", then cell C1 on Sheet1 in the workbook Merge.xls will contain "My Document"
Are you summing data, C1 in One.xls contains 10, C1 in Two.xls contains 10, so C1 in the Merge.xls file will contain 20.
2. Am I correct in thinking that your 10 files are ten individual workbooks.
It would be helpful if you explained this - also is the data in each workbook in a worksheet named "Sheet1" or is it in another worksheet.
3. Can you explain more about the sequence
c1,c2,c3,c4,c5,c6 f1 c7, f2 c7 ....
Is c7 meant to appear twice.
What happens after f2, c7. Does the sequence repeat e.g., f1, c7, f2, c8, f3, c9 ...
or is it
f1, c7, f2, c7, f3, c7 ....
and where does this end - what are the last cells to be merged.
4. It would help to know if you are looking for a one time merge, i.e., get the data from the 10 workbooks, and merge them and save
will the data in the 10 workbooks be changing and you need formulas that link to the relevant cells in all 10 workbooks.
Here is a formula that adds the values in cell A1 in Sheet1 in four workbooks:
='C:\temp\[File1.xls]Sheet1'!$A$1 + 'C:\temp\[File2.xls]Sheet1'!$A$1 + 'C:\temp\[File3.xls]Sheet1'!$A$1 + 'C:\temp\[File4.xls]Sheet1'!$A$1Each workbook is in the same folder on Drive C:, but part of the formula needs the path to each file, so the formula quickly becomes rather long.
If you can say what you are trying to achieve, it might help in coming up with a solution.