What might be the root of the problem is that the referenced files were initially created in the same workbook and saved as separate files afterwards. So after each new worksheet was created Excel would count it in order. Therefore, even though the worksheet of the referenced file is named Sheet1 but in the VB editor it's shown as Sheet9(Sheet1). Basically, the macro is looking for Sheet1 in the referenced workbook but finds only Sheet9(Sheet1) and therefore return an error message. You think there could be any workaround?

Thanks!