Collation of worksheets

[akulka58]

Hi,

Request you to please help me in below issue.

I need to collate data from multiple workbooks. I have near about 50 workbooks in my folder. In sample screen shot I have provided few. I need to collate all worksheet into from 50 files into one workbook. The naming conventions of files are like xxx-abc-xx. Please refer screen shot. The problem I am facing is each file contains same worksheet

table

data2

data3

data4

I need to collate these worksheets from all files with small change that is while collating data from workbooks it also put file name before that. Please see below tabs

abc - table

abc - data2

abc - data3

abc - data4

if I did not do this then I will never come to know from which file code has taken data. Code should not take blank sheets from files.

The last thing is that apart from above mentioned tabs if there is another tab then code should collate that tab in our base sheet.

Please help me in this

Thanks