» Copy cells to all workbooks in a folder using VBA in Microsoft Excel

VBA macro tip contributed by Ron de Bruin, Microsoft MVP - Excel This example will copy the range "a1:c5" from the first worksheet in the workbook where the code is in to the first sheet in the same range of all workbooks in the folder c:\Data.
You can change the range and the sheet name as you like.
There is a macro that does a normal copy and one that is only copy the values.

Sub TestFile()
    Dim basebook As Workbook
    Dim mybook As Workbook
    Dim sourceRange As Range
    Dim destrange As Range
    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))
                Set sourceRange = basebook.Worksheets(1).Range("a1:c5")
                Set destrange = mybook.Worksheets(1).Range("a1")
                sourceRange.Copy destrange
                mybook.Close True
            Next i
        End If
    End With
    Application.ScreenUpdating = True
End Sub