Copy from 1:Many WBs

Question

    Sub CopyPasteData()
    
    DataDir = "C:\My Documents\Test\"
    ChDir (DataDir)
    File = Dir("*.xlsm")

 
    While Nextfile <> ""                                               

        For Each f MasterWB.Names("nameList").RefersToRange   
            If f = File Then                                 

                newValues = MasterWB.Sheets("Master").Range("L4:U4").Value
                Workbooks.Open (File)
                Workbooks(Nextfile).Sheets("Report1").Unprotect Password:="qwedsa"


                Workbooks(Nextfile).Sheets("Report1").Range("H10:R10") = newValues
                Workbooks(File).Protect Password:="qwedsa"
                Workbooks(File).Save
                Workbooks(File).Close

            End If

        Next fileCell

        Nextfile = Dir()

    Wend

End Sub

I can't seem to iterate through the named range and appropriately copy and paste the lookup values between workbooks. I'm not sure how to make my loops work. How do I make this code to work?

Answer 1

Instead of opening every file and then checking to see if it's in your list, you can loop over the list and check to see if there's a matching file, and only then open and update it.

Sub CopyandPasteData()
    
    Const PW As String = "qwedsa" 'use constants for fixed values
    Dim fldr As String, wbMaster As Workbook, wsMaster As Worksheet
    Dim c As Range, wb As Workbook

    fldr = "C:\My Documents\Test\"
    
    Set wbMaster = ActiveWorkbook
    Set wsMaster = wbMaster.Worksheets("Master")
    
    For Each c In wsMaster.Range("B9:B111").Cells
        If Len(Dir(fldr & c.Value)) > 0 Then          'file exists?
            Set wb = Workbooks.Open(fldr & c.Value)
            With wb.Sheets("Report1")
                .Unprotect Password:=PW    'unprotect sheet and copy data
                .Range("H10:R10").Value = wsMaster.Range("L4:U4").Value
                .Protect Password:=PW
            End With
            wb.Close savechanges:=True
            ' ### fix the line below to reference the correct range ###
            c.Value = wbMaster.Worksheets("sheetName").Range("B4").Value
        End If
    Next c

End Sub