I would like to copy a specific worksheet from different excel files to one and the same worksheet (named Overall_List) of a workbook named 'Aggregate_File.xlsx'.
Here are the details for the macro:
6 Excel-files reside in a folder with the following path: C:\MyDocuments\MyFiles\...
5 of these excel workbooks have a worksheet named 'Prod_List' which need to be copied to the worksheet named 'Overall_List' of the file named 'Aggregate_File.xlsx
The macro should only copy the current range starting from A2 of these worksheets named 'ProdList'. The copied contents of these worksheets (Prod_List) is to be inserted one after the other to this worksheet 'Overall_List' of the 'Aggregate_File.xlsx.
Any contents in this worksheet 'Overall_List' is to be deleted, i.e. the copied contents overwrites any existing contents.
The formatting of the copied cells is to be retained.
Help is very much appreciated. Thank you very much in advance.