Hi I am a beginner in VBA.

I have a unique requirement to copy and paste data from a folder of excel files to another workbook having reserved (named) tabs for each source files data.

Explaining the scenario -

There are many excel files in a specific folder. I want help in VBA code which can loop through the folder and do the following -

1. Copy data from each file having a single sheet ("Sheet1").
2. Paste the copied data to a dedicated named sheet of a master workbook.
3. Erase previous months data in the target sheet before pasting the fresh data.


There is a catch -

The master workbook and the source files are at same location.
The source files need not be opened, Since It will result in 100s of opened file.



Please help in this, I had been searching this over internet (unsuccessfully)

I have to do this manually and it is tedious to be honest.

Thanks