Solved: Changes in all Workbooks in One Time

[ayazgreat]

Hi

I have 100 files (workbooks) in c folder in my documents with thier different cities names but they all have same formate how could it be possible to make changes in all workbooks in same time? could I have to open all workbooks one by one and change it one by one?

Suppose I want to clear contents in all workbook in sheet1 in cells b2 to d50. May I have to open all workbooks and do the same?

[dominicb]

Good morning ayazgreat

The code below will go through all .xls files in the stated directory, will open each file, delete the range B2:50 in Sheet1, save the file and close it again. The first and last lines turn off the screenupdating to reduce screenflicker and speed up operation - comment them out if you'd rather see what's going on.

** Back the source directory up now**

Sub AmendFiles()
Application.ScreenUpdating = False
Dim WkBk As Workbook, MyFile As String
MyFile = Dir("D:\test\*.xls") 'Change this to your path
Do While MyFile <> ""
Set WkBk = Workbooks.Open("D:\test\" & MyFile) 'Change this to your path
Sheets("Sheet1").Range("B2:D50").ClearContents
WkBk.Save
WkBk.Close
MyFile = Dir
Loop
Application.ScreenUpdating = True
End Sub
HTH

DominicB

[mdmackillop]

Hi Dominic,
On this site, use the VBA tags/button to format your code as shown.
Regards
MD

[dominicb]

Hi Dominic,
On this site, use the VBA tags/button to format your code as shown.
Regards
MD

Hi mdmackillop

Did I not tag my code?
Sorry. Thought I had - I normally do.

DominicB

[mdmackillop]

Hi Dominic,
You did, but use the VBA tags instead of Code to get the formatting.

[dominicb]

You did, but use the VBA tags instead of Code to get the formatting.

Ha!!
I didn't know that.:thumb
Thanks for doing it for me. I'll use that in future - it does look much better ...

DominicB

[ayazgreat]

Hi Dominic,

Thank you very much for help it is really helpful for me.