How to edit 2 cell fields in multiple excel files in a folder/subfolders automatically using VBA based on starting name of file
For example I have a root folder of c:\master templates
Under that folder I have subfolders with clients names as the folders
Under each client folder I have folders with job numbers
Under those job number folders are the excel files that I want to change
They all start with the name of set_up sheet *
There could be 2 set_up sheet excel files or 3 or 5, it all varies based on client and job, but they are all named the same starting with "set_up sheet*"
I want to change 2 cells in each of these set_up sheet*.xls files for every directory/subdirectory that contains those files
Basically replace 2 fields
M14 with "Stick Out"
S14 with "Vending #"
Then I want to save each excel file back and close and then loop through and do the same thing for all the other excel files named the same thing in each directory/sub
Here is the code I have so far which I know isn't correct. It's just what I wrote by watching a youtube video on it. I know I am on the right track I just need some help
Dim MyFile As String
Dim Filepath As String
Dim q As Long
Filepath = "C:\mastertemplates\"
MyFile = Dir(Filepath)
Do While Len(MyFile) > 0
If MyFile = "bookz.xlsm" Then
Workbooks.Open (Filepath & MyFile)
For q = 1 To Application.Worksheets.Count
Range("M14") = "Stick Out"
Range("S14") = "Vending #"
MyFile = Dir
Thanks in advance