Macro loop to copy data from master file to all files in folder

[321richard]

I need help writing a macro loop that will copy data from a master file (always open) to all files in a folder (over 80 files in the folder). My current, inefficent method is to explictly state the file names and their location and the master file which is already open. Any help would be much appreciated!!!

The following code shows

Sub LoopAllFiles()
'OPEN FIRST FILE NAMED "FILE 001.XLSX" AND COPY/PASTE DATA FROM MASTER FILE
    Windows("Master File.xlsm").Activate
    'file is always open
    Range("A71:H87").Select
    Selection.Copy
    
    Workbooks.Open filename:="C:\Users\Richard\Desktop\Test Macro Loop\File 001.xlsx"
    'need to open all files in the folder without explictly stating their name
    
    Range("A71").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    ActiveWorkbook.Save
    ActiveWindow.Close
'OPEN SECOND FILE NAMED "FILE 002.XLSX" AND COPY/PASTE DATA FROM MASTER FILE
    Windows("Master File.xlsm").Activate
    'file is always open
    Range("A71:H87").Select
    Selection.Copy
    
    Workbooks.Open filename:="C:\Users\Richard\Desktop\Test Macro Loop\File 002.xlsx"
    'need to open all files in the folder without explictly stating their name
    
    Range("A71").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    ActiveWorkbook.Save
    ActiveWindow.Close
           
'OPEN THIRD FILE NAMED "FILE 003.XLSX" AND COPY/PASTE DATA FROM MASTER FILE
    Windows("Master File.xlsm").Activate
    'file is always open
    Range("A71:H87").Select
    Selection.Copy
    
    Workbooks.Open filename:="C:\Users\Richard\Desktop\Test Macro Loop\File 003.xlsx"
    'need to open all files in the folder without explictly stating their name
    
    Range("A71").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    ActiveWorkbook.Save
    ActiveWindow.Close

End Sub

 

[JLGWhiz]

This uses the Dir function to get the files from the folder by file name. It assumes that all of the files begin with the word "File" and followed by various numerical serial numbers. It opens the file, copies the range from the master file sheet to the range in the target worksheet, then saves and closes the workbook. It will loop through all files which meet the name criteria.

Sub LoopAllFiles2()
Dim wb As Workbook, mf As Workbook, sh1 As Worksheet, sh2 As Worksheet, rng As Range, fPath As String, fName As String
Set mf = Workbooks("Master File.xlsm")
fPath = "C:\Users\Richard\Desktop\Test Macro Loop\"
fName = Dir(fPath & "File*.xlsx")
Set sh1 = mf.Sheets(1) 'Edit sheet name(this is your master sheet)
Set rng = sh1.Range("A71:H87")
    Do
        Set wb = Workbooks.Open(fPath & fName)
        Set sh2 = wb.Sheets(1) 'Edit sheet name (this is the target sheet assumed to be the same in all 80 files)
        rng.Copy sh2.Range("A71")
        wb.Close True
        fName = Dir
    Loop While fName <> ""
End Sub

I did not test the procedure in run mode, so you will need to do that.