Seems like this should work. HTH. Dave
Code:
Sub test()
Dim FSO As Object, FlDr As Object, Fl As Object
Dim sht As Worksheet
Set FSO = CreateObject("scripting.filesystemobject")
Set FlDr = FSO.GetFolder("C:\MOS\")
Application.ScreenUpdating = False
Application.DisplayAlerts = False
On Error Resume Next
For Each Fl In FlDr.Files
If Fl.Name Like "*.xls*" Then
Workbooks.Open Filename:=Fl
For Each sht In Fl.Sheets
If sht.Name = "Control" Then
Workbooks(Fl.Name).Sheets("Control").Range("A" & 1) = _
ThisWorkbook.Sheets("Sheet1").Range("A" & 1).Value
Exit For
End If
Next sht
Workbooks(Fl.Name).Close SaveChanges:=True
End If
Next Fl
Set FlDr = Nothing
Set FSO = Nothing
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub
Thanks a lot for help.
How to exclude file DataFile.xlsm from the rest of the files. The macros is supposed to run from DataFile. When I run it it gives message that DataFile is already open and an attempt to open it again will not save any changes, and I found it out only after changing DisplayAlerts to True, otherwise it would run with no error messages but at the same time id does not make any changes to files in the directory specified. Thanks in advance for any suggestions.
Sub LoopThroughNdNovice()
Dim FSO As Object, FlDr As Object, Fl As Object
Dim sht As Worksheet
Set FSO = CreateObject("scripting.filesystemobject")
Set FlDr = FSO.GetFolder("C:\Users\User\Downloads\Trading\Test\Master DataFile")
Application.ScreenUpdating = True
Application.DisplayAlerts = True
On Error Resume Next
For Each Fl In FlDr.Files
If Fl.Name Like "*.xls*" Then
Workbooks.Open fileName:=Fl
For Each sht In Fl.Sheets
If sht.Name = "Control" Then
Workbooks(Fl.Name).Sheets("Control").Range("A" & 1) = _
ThisWorkbook.Sheets("Sheet1").Range("A" & 1).Value
Exit For
End If
Next sht
Workbooks(Fl.Name).Close SaveChanges:=True
End If
Next Fl
Set FlDr = Nothing
Set FSO = Nothing
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub