» Copy a sheet from each workbook into your workbook in a folder using VBA in Microsoft Excel

VBA macro tip contributed by Ron de Bruin, Microsoft MVP - Excel

• The macro will copy a part of the first worksheet of every file that is in the folder C:\Data to the first worksheet of your workbook.
• The first macro does a normal copy and second macro copy the values.

It will copy the first sheet of each workbook into the workbook where the code is in.
The sheet name is the name of the workbook.

Sub CopySheet()
Dim basebook As Workbook
Dim mybook As Workbook
Dim i As Long
    Application.ScreenUpdating = False
    With Application.FileSearch
        .NewSearch
        .LookIn = "C:\Data"
        .SearchSubFolders = False
        .FileType = msoFileTypeExcelWorkbooks
        If .Execute() > 0 Then
            Set basebook = ThisWorkbook
            For i = 1 To .FoundFiles.Count
                Set mybook = Workbooks.Open(.FoundFiles(i))
                    mybook.Worksheets(1).Copy after:= _
                    basebook.Sheets(basebook.Sheets.Count)
                    ActiveSheet.Name = mybook.Name
                mybook.Close
            Next i
        End If
    End With
    Application.ScreenUpdating = True
End Sub

For this sub(TestFile4_values) you must have unprotected worksheets, or unprotect them in the code.

Sub CopySheetValues()
    Dim basebook As Workbook
    Dim mybook As Workbook
    Dim i As Long
    Application.ScreenUpdating = False
    With Application.FileSearch
        .NewSearch
        .LookIn = "C:\Data"
        .SearchSubFolders = False
        .FileType = msoFileTypeExcelWorkbooks
        If .Execute() > 0 Then
            Set basebook = ThisWorkbook
            For i = 1 To .FoundFiles.Count
                Set mybook = Workbooks.Open(.FoundFiles(i))
                mybook.Worksheets(1).Copy after:= _
                                                 basebook.Sheets(basebook.Sheets.Count)
                ActiveSheet.Name = mybook.Name
                With ActiveSheet.UsedRange
                    .Value = .Value
                End With
                mybook.Close
            Next i
        End If
    End With
    Application.ScreenUpdating = True
End Sub