nobodysfool
New Member
- Joined
- Dec 21, 2022
- Messages
- 2
- Office Version
- 365
- 2021
- 2019
- 2016
- Platform
- Windows
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:
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
VBA Code:
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()