link multiple files

[Tumbad]

Hi Friends,

I need to develop macro for one of my day to day activity which involves pasting data from 14 different excel files in single excel file in 14 different sheets. Format of source and destination files name is same except date change.

Destination file is Xxx.ddmmyy.xlsx

Source files format is as below.
XXXXX_XXXXXXX_ddmmyyyy.xls

data in first sheet always of all 14 files. and worksheet name is same across all 14 files.

Always clear data in destination sheets and paste updated data. There is one to one co-relation between which file to go in which sheet however worksheet name in destination file can be changed if required.

Warm regards,
Tumbad

 

[Tumbad]

Apologies Yongle, I was on leave for few days hence couldn't check this. I am back today. Will test over the weekend and confirm. Thanks a ton for your efforts in writing this.

 

[Tumbad]

Thanks Yongle, this works like magic. After doing some testing, below are small clarifications required.

1. Is there number of character limit while searching file? I have one file name which reads as below, which is exactly what i have put in macro, but it doesnt fetch that file.

"ORAN_Redesignes_Below_thresholde_rates_adjust_20200921"

Rest all files got fetched, except this.

2. While cleaning the workseet everytime macro runs, for "Dest.Sheets(x + 2).Cells.ClearContents" want to delete data from column A to L only in all destination sheets.

3. Macro needs to run in checking file itself i.e. in destination file itself and after macro run in completed, simply save the file with date like FV210920 in non macro format in same folder.

Thanks a ton and really appreciate your help so far.

Tumbad

 

[Yongle]

Glad code does what you require
Busy for next day or so - will update thread when clear of other duties

 

[Yongle]

I have not forgotten you, but I am fully committed elsewhere until tomorrow