Enter a constant into Ten Workbooks

  • Thread starter Thread starter JMay
  • Start date Start date
J

JMay

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,,,
 
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
 
Thank you so much,, This is awesome...
JMay

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
Click to expand...
 
Back
Top