# Loop issues to find match criteria from master file to copy and paste matching individual files



## nobodysfool (Dec 21, 2022)

Newbie here. I am still learning the different loops in VBA. When I debug the code, the For Each loop doesn't seem to work at all and I'm not sure how to fix it. I was hoping someone could help me!

I am looking to do the following with this code:

Refer to named range from master worksheet from a master workbook (saved in a different location but is open)
Loop through all the excel enabled macro files from one folder
Match the file name from each of these files with the named range from master workbook
If there is a match, then copy values only from lookup range from the master worksheet and paste values onto the matched file (worksheet called Report1)
All the Report1 worksheets in the individual files are password protected so unprotect, make the changes, protect, save and then close

Repeat this process until the named range list is exhausted


```
Sub CopyPasteData()
    
    Dim DataDir As Object
    Dim Nextfile As Workbook
    Dim MasterWB As Workbook
    Dim fileCell As String
    Dim newValues As Long
    
    DataDir = "C:\My Documents\Test\"
    ChDir (DataDir)
    Nextfile = Dir("*.xlsm")
    Set MasterWB = ActiveWorkbook                                       'master workbook to extract data from

 
    While Nextfile <> ""                                                'iterate through all macro enabled files in the subfolder

        For Each fileCell In MasterWB.Names("nameList").RefersToRange   'loop through all cells in the named range
            If fileCell = Nextfile Then                                 'if cell from named range matches with workbook, then replace over range of cells below

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


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

            End If

        Next fileCell

        Nextfile = Dir()
```


----------



## nobodysfool (Dec 21, 2022)

I can't seem to find the edit button but the code is missing 2 lines:

    Wend

End Sub


----------



## Peter_SSs (Dec 21, 2022)

nobodysfool said:


> I can't seem to find the edit button


You will not be able to edit anything until you have been here a bit longer and made a few more posts. 



nobodysfool said:


> the code is missing 2 lines:


I have added them in for you.


----------

