We can try and simplify this way. In the master file where you are going to consolidate your data, you can have one sheet (possibly hidden) which will contain all these parameters. For e.g. Column A - File Name, Column B - Source Worksheet name, Column C - Column to be copied, Column D - Destination sheet name.

Then you can have the macro loop through each row of this sheet and action accordingly. If you use this method, if there are any changes to your files, you can just make the changes to this sheet instead of changing the code.

Works for you?