Hi All
Could you please helpme with my macro?
I need to update thesame cell in the same tab of multiple excel sheets organized in many subfoldersof one folder. E.g. I have a folder called “June” that contains folder “test”and folder “a”. Folders “test” and “a” contains number of excel files withdifferent names. All of them have sheet “test”. I need to have “6” entered in “A1”cell of this sheet in every file. I will need to have the same folder for July,and all the files in subfolders “a” and “test” to be updated with “7” in “A1”, “August”folder with “8” etc.
What I have done sofar: I created excel file “macro” in “June” folder with sheet “update1” and “6”in cell A1. I run below macro from this file and it does update all my excelsin “test” and “a” subfolders in folder “June”. If I copy “June” with all thesubfolders, rename it to “July”, change the cell A1 to “7” in “marco” “update1”,save the file and run the code, it updates all my files in July with 7. So farso good…it all works, as long as my “June” folder is stored on Desktop.
Now I place “June” inone of the folders on network drive (where I actually need it), and it does notwork anymore. It gives no error message, but just does nothing with the files.Do you have any idea what is the issue and how to overcome it?
While Nextfile<> ""
newvalue =ActiveWorkbook.Sheets("update1").Range("A1").Value
Workbooks.Open (Nextfile)
Workbooks(Nextfile).Sheets("test").Range("A1")= newvalue
Workbooks(Nextfile).Save
Workbooks(Nextfile).Close
Nextfile = Dir()
Wend
MyDir = ActiveWorkbook.Path
DataDir = MyDir & "\a"
ChDir (DataDir)
Nextfile = Dir("*.xls")
While Nextfile <> ""
newvalue =ActiveWorkbook.Sheets("update1").Range("A1").Value
Workbooks.Open (Nextfile)
Workbooks(Nextfile).Sheets("test").Range("A1")= newvalue
Workbooks(Nextfile).Save
Workbooks(Nextfile).Close
Nextfile = Dir()
Wend
End Sub
Could you please helpme with my macro?
I need to update thesame cell in the same tab of multiple excel sheets organized in many subfoldersof one folder. E.g. I have a folder called “June” that contains folder “test”and folder “a”. Folders “test” and “a” contains number of excel files withdifferent names. All of them have sheet “test”. I need to have “6” entered in “A1”cell of this sheet in every file. I will need to have the same folder for July,and all the files in subfolders “a” and “test” to be updated with “7” in “A1”, “August”folder with “8” etc.
What I have done sofar: I created excel file “macro” in “June” folder with sheet “update1” and “6”in cell A1. I run below macro from this file and it does update all my excelsin “test” and “a” subfolders in folder “June”. If I copy “June” with all thesubfolders, rename it to “July”, change the cell A1 to “7” in “marco” “update1”,save the file and run the code, it updates all my files in July with 7. So farso good…it all works, as long as my “June” folder is stored on Desktop.
Now I place “June” inone of the folders on network drive (where I actually need it), and it does notwork anymore. It gives no error message, but just does nothing with the files.Do you have any idea what is the issue and how to overcome it?
While Nextfile<> ""
newvalue =ActiveWorkbook.Sheets("update1").Range("A1").Value
Workbooks.Open (Nextfile)
Workbooks(Nextfile).Sheets("test").Range("A1")= newvalue
Workbooks(Nextfile).Save
Workbooks(Nextfile).Close
Nextfile = Dir()
Wend
MyDir = ActiveWorkbook.Path
DataDir = MyDir & "\a"
ChDir (DataDir)
Nextfile = Dir("*.xls")
While Nextfile <> ""
newvalue =ActiveWorkbook.Sheets("update1").Range("A1").Value
Workbooks.Open (Nextfile)
Workbooks(Nextfile).Sheets("test").Range("A1")= newvalue
Workbooks(Nextfile).Save
Workbooks(Nextfile).Close
Nextfile = Dir()
Wend
End Sub