Enter a constant into Ten Workbooks
 

In my folder My Documents\Test
I have ten xls files, nothing else. They are not Open
In Cell C10 of Sheet1 of each file
I'd like to run a macro and it overwrite cell C10 with a number from an
Input Box.
Can this be done?

Thanks in advance,,,

Enter a constant into Ten Workbooks
 

Sub WriteToMany()
Const myPath = "My Documents\Test\"
Dim rep As String
Dim wName As String
Dim wBook As Workbook

rep = InputBox("Enter new value to
overwrite", "Overwrite in all files")
If rep = "" Then Exit Sub

Application.ScreenUpdating = False
wName = Dir(myPath & "*.xls")

Do While wName < ""
Set wBook = Workbooks.Open(myPath & wName)
wBook.Worksheets("Sheet1").Range("C10").Value = rep
wBook.Close SaveChanges:=True
wName = Dir()
Loop
Set wBook = Nothing

Application.ScreenUpdating = True
End Sub

Kevin Beckham

-----Original Message-----
In my folder My Documents\Test
I have ten xls files, nothing else. They are not Open
In Cell C10 of Sheet1 of each file
I'd like to run a macro and it overwrite cell C10
with a number from an
Input Box.
Can this be done?

Thanks in advance,,,


.

Enter a constant into Ten Workbooks
 

Thank you so much,, This is awesome...
JMay

wrote in message
...
Sub WriteToMany()
Const myPath = "My Documents\Test\"
Dim rep As String
Dim wName As String
Dim wBook As Workbook

rep = InputBox("Enter new value to
overwrite", "Overwrite in all files")
If rep = "" Then Exit Sub

Application.ScreenUpdating = False
wName = Dir(myPath & "*.xls")

Do While wName < ""
Set wBook = Workbooks.Open(myPath & wName)
wBook.Worksheets("Sheet1").Range("C10").Value = rep
wBook.Close SaveChanges:=True
wName = Dir()
Loop
Set wBook = Nothing

Application.ScreenUpdating = True
End Sub

Kevin Beckham

-----Original Message-----
In my folder My Documents\Test
I have ten xls files, nothing else. They are not Open
In Cell C10 of Sheet1 of each file
I'd like to run a macro and it overwrite cell C10
with a number from an
Input Box.
Can this be done?

Thanks in advance,,,


.