vba to copy specific sheet tab from several files folder to consolidation workbook

I have a lot of workbooks in folder C:\BACKUPDOC.

All the workbooks have sheets multiple sheets but my goal is extract from these workbooks a specific sheets named Infos_Container.

The header starts at A3 to AS 3

Data starts at A4 to AS 4 still to last data row.

Among the columns there may be empty cells or empty columns.

From folder C:\\BACKUP copy all sheets in that folder which has sheet name Infos_Container. to a specific workbook consolidation in same folder.

then rename tab sheet as per cell H4 of each workbook.

Attached sample results (consolidation)with one dummy file .

In sample results there are two sheet tab as example.
All files have same format

Looking a vba code to achieve this.

Since H4 in 2 workbooks are the same "Data", it will end up a blank sheet, due to not allowed to have same sheet names.

Please Login or Register  to view this content.

Edit: typo