Loop through a folder and copy data from files to another WB

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):crying:

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

Thanks

Code:

---

Option Explicit


Sub test()
    Dim ws As Worksheet
    Dim p As String
    Dim wbn As String
   
    Set ws = ActiveSheet
    ws.UsedRange.Offset(1).ClearContents
   
    p = ThisWorkbook.Path & "\"
    wbn = Dir(p & "*.xlsx")
   
    Do While wbn <> ""
        With GetObject(p & wbn).Sheets("sheet1")
            .Cells(1).Offset(1).Copy ws.Cells(Rows.Count, 1).End(xlUp).Offset(1)
            .Parent.Close False
        End With
        wbn = Dir()
    Loop
   
End Sub

---

Appreciate your help Mana. many thanks :)


Unfortunately, I think I was not able to put my situation in words properly, apologies for this.

I will try to explain again -

I have a folder where I have 114 excel files and 1 master workbook.

The master workbook has 114 worksheets in it, each dedicated for data to be copied from the source excel files.


I needed a VBA code, which can loop through the folder select each source file at once and copy its data as it is in the dedicated named sheet of the master file.

Hope I am clear this time.

Code:

---

Sub test2()
    Dim ws As Worksheet
    Dim p As String
    Dim wbn As String
   
    p = ThisWorkbook.Path & "\"
   
    For Each ws In Worksheets
        ws.UsedRange.Clear
        wbn = Dir(p & ws.Name & ".xlsx")
        If wbn <> "" Then
            With GetObject(p & wbn)
                .Sheets("sheet1").Cells(1).CurrentRegion.Copy ws.Cells(1)
                .Close False
            End With
        End If
    Next


End Sub

---

thanks for your try.

But this solution doesn't solve my problem mentioned.

However, I can use it for another scenario.

However, my situation remains unsolved :(

This is what actually required -

I have a folder where I have 114 excel files and 1 master workbook.

the master workbook has 114 tabs which are already named and they should have data copied from a specific source file.
For example, if the name of the sheet (Tab) in the master workbook is "West_Data" , than it should be filled with the data from a file which has West_Data in its name, lets say - "Jan_West_Data_NA.xlsx".

This is the scenario for all 114 files in the folder and all 114 sheets in the master workbook.


I needed a VBA code, which can loop through the folder select each source file at once and copy its data as it is in the dedicated named sheet of the master file.

Hope I am making sense this time.

Plz help me, I am out of options and need help.

thanks in advance.

Code:

---

wbn = Dir(p & "*_" & ws.Name & "_*.xlsx")

---

Thank you So very much for your reply,

But the code is not coping the files, though it throws no error.

Evan, I tried to change the the files name extensions and the names also, But still the data is not getting copied.