Here is the macro that I have and have used to change a test folder
with test workbooks. Not all of the sheets have the same same but
they are all sheet1. Can you help me amend this to have it go to
sheet1 of each workbook?

Thanks,
Jay

Sub changeFormulas()
Dim WB As Workbook
Dim fs
Dim i As Integer

Application.DisplayAlerts = False
Application.ScreenUpdating = False

Set fs = Application.FileSearch

With fs
.LookIn = "C:\Documents and Settings\JLClyde\Desktop\Steve" '
change this to the folder you want
.Filename = "*.xls"
If .Execute 0 Then ' checks to see if there are excel files
in the folder specified
For i = 1 To .FoundFiles.Count ' for each file found
fname = .FoundFiles(i) 'sets workbook name to be
opened
Set WB = Workbooks.Open(fname) 'opens the file
With WB
Sheet1.Range("B10") = "=IF(E5=0,0,E5/C10/C13)"
.Close SaveChanges:=True
End With
Next
End If
End With

Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

Does this mean that you want to change the worksheet that has a codename of
Sheet1 or do you want to change the leftmost worksheet in the workbook?

If it's the leftmost worksheet, you can use:
worksheets(1).Range("B10").formula = "=IF(E5=0,0,E5/C10/C13)"

If it's the codename, you could use:

Option Explicit
Sub changeFormulas()
Dim WB As Workbook
Dim fs As FileSearch
Dim i As Long
Dim Wks As Worksheet
Dim fName As String

Application.DisplayAlerts = False
Application.ScreenUpdating = False

Set fs = Application.FileSearch

With fs
' change this to the folder you want
.LookIn = "C:\Documents and Settings\JLClyde\Desktop\Steve"
.LookIn = "C:\my documents\excel\test"
.Filename = "*.xls"
' checks to see if there are excel files in the folder specified
If .Execute 0 Then
For i = 1 To .FoundFiles.Count ' for each file found
fName = .FoundFiles(i) 'sets workbook name to be opened
Set WB = Workbooks.Open(fName) 'opens the file
With WB
Set Wks = Nothing
For Each Wks In WB.Worksheets
If LCase(Wks.CodeName) = LCase("sheet1") Then
Exit For
End If
Next Wks

If Wks Is Nothing Then
MsgBox "no sheet with a codename Sheet1 in: " _
& vbLf & WB.FullName
Else
Wks.Range("B10").Formula = "=IF(E5=0,0,E5/C10/C13)"
End If

.Close savechanges:=Not (Wks Is Nothing)
End With
Next
End If
End With

Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

jlclyde wrote:

Here is the macro that I have and have used to change a test folder
with test workbooks. Not all of the sheets have the same same but
they are all sheet1. Can you help me amend this to have it go to
sheet1 of each workbook?

Thanks,
Jay

Sub changeFormulas()
Dim WB As Workbook
Dim fs
Dim i As Integer

Application.DisplayAlerts = False
Application.ScreenUpdating = False

Set fs = Application.FileSearch

With fs
.LookIn = "C:\Documents and Settings\JLClyde\Desktop\Steve" '
change this to the folder you want
.Filename = "*.xls"
If .Execute 0 Then ' checks to see if there are excel files
in the folder specified
For i = 1 To .FoundFiles.Count ' for each file found
fname = .FoundFiles(i) 'sets workbook name to be
opened
Set WB = Workbooks.Open(fname) 'opens the file
With WB
Sheet1.Range("B10") = "=IF(E5=0,0,E5/C10/C13)"
.Close SaveChanges:=True
End With
Next
End If
End With

Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

--

Dave Peterson

On Mar 24, 4:18*pm, Dave Peterson wrote:
Does this mean that you want to change the worksheet that has a codename of
Sheet1 or do you want to change the leftmost worksheet in the workbook?

If it's the leftmost worksheet, you can use:
worksheets(1).Range("B10").formula = "=IF(E5=0,0,E5/C10/C13)"

If it's the codename, you could use:

Option Explicit
Sub changeFormulas()
* * Dim WB As Workbook
* * Dim fs As FileSearch
* * Dim i As Long
* * Dim Wks As Worksheet
* * Dim fName As String

* * Application.DisplayAlerts = False
* * Application.ScreenUpdating = False

* * Set fs = Application.FileSearch

* * With fs
* * * * ' change this to the folder you want
* * * * .LookIn = "C:\Documents and Settings\JLClyde\Desktop\Steve"
* * * * .LookIn = "C:\my documents\excel\test"
* * * * .Filename = "*.xls"
* * * * ' checks to see if there are excel files in the folder specified
* * * * If .Execute 0 Then
* * * * * * For i = 1 To .FoundFiles.Count ' for each file found
* * * * * * * * fName = .FoundFiles(i) 'sets workbook *name to be opened
* * * * * * * * Set WB = Workbooks.Open(fName) 'opens the file
* * * * * * * * With WB
* * * * * * * * * * Set Wks = Nothing
* * * * * * * * * * For Each Wks In WB.Worksheets
* * * * * * * * * * * * If LCase(Wks.CodeName) = LCase("sheet1") Then
* * * * * * * * * * * * * * Exit For
* * * * * * * * * * * * End If
* * * * * * * * * * Next Wks

* * * * * * * * * * If Wks Is Nothing Then
* * * * * * * * * * * * MsgBox "no sheet with a codename Sheet1 in: " _
* * * * * * * * * * * * * * * * * *& vbLf & WB.FullName
* * * * * * * * * * Else
* * * * * * * * * * * * Wks.Range("B10").Formula = "=IF(E5=0,0,E5/C10/C13)"
* * * * * * * * * * End If

* * * * * * * * * * .Close savechanges:=Not (Wks Is Nothing)
* * * * * * * * End With
* * * * * * Next
* * * * End If
* * End With

* * Application.ScreenUpdating = True
* * Application.DisplayAlerts = True
End Sub





jlclyde wrote:

Here is the macro that I have and have used to change a test folder
with test workbooks. *Not all of the sheets have the same same but
they are all sheet1. *Can you help me amend this to have it go to
sheet1 of each workbook?

Thanks,
Jay

Sub changeFormulas()
* * Dim WB As Workbook
* * Dim fs
* * Dim i As Integer

* * Application.DisplayAlerts = False
* * Application.ScreenUpdating = False

* * Set fs = Application.FileSearch

* * With fs
* * * * .LookIn = "C:\Documents and Settings\JLClyde\Desktop\Steve" '
change this to the folder you want
* * * * .Filename = "*.xls"
* * * * If .Execute 0 Then ' checks to see if there are excel files
in the folder specified
* * * * * * For i = 1 To .FoundFiles.Count ' for each file found
* * * * * * * * fname = .FoundFiles(i) 'sets workbook *name to be
opened
* * * * * * * * Set WB = Workbooks.Open(fname) 'opens the file
* * * * * * * * With WB
* * * * * * * * * * Sheet1.Range("B10") = "=IF(E5=0,0,E5/C10/C13)"
* * * * * * * * * * .Close SaveChanges:=True
* * * * * * * * End With
* * * * * * Next
* * * * End If
* * End With

* * Application.ScreenUpdating = True
* * Application.DisplayAlerts = True
End Sub

--

Dave Peterson- Hide quoted text -

- Show quoted text -

Dave,
It is the one that is the left most positoin. I was going around
with worksheets, sheet, sheets. Thank you very much this will do the
trick nicely.
Jay

Oops. I had a typo:

.worksheets(1).Range("B10").formula = "=IF(E5=0,0,E5/C10/C13)"

That leading dot is very important. Maybe not so in this case. Without the
dot, the worksheets(1) reference will refer to the activeworkbook. But since WB
was just opened, it should be active.

I'd still add that leading dot.

jlclyde wrote:

On Mar 24, 4:18 pm, Dave Peterson wrote:
Does this mean that you want to change the worksheet that has a codename of
Sheet1 or do you want to change the leftmost worksheet in the workbook?

If it's the leftmost worksheet, you can use:
worksheets(1).Range("B10").formula = "=IF(E5=0,0,E5/C10/C13)"

If it's the codename, you could use:

Option Explicit
Sub changeFormulas()
Dim WB As Workbook
Dim fs As FileSearch
Dim i As Long
Dim Wks As Worksheet
Dim fName As String

Application.DisplayAlerts = False
Application.ScreenUpdating = False

Set fs = Application.FileSearch

With fs
' change this to the folder you want
.LookIn = "C:\Documents and Settings\JLClyde\Desktop\Steve"
.LookIn = "C:\my documents\excel\test"
.Filename = "*.xls"
' checks to see if there are excel files in the folder specified
If .Execute 0 Then
For i = 1 To .FoundFiles.Count ' for each file found
fName = .FoundFiles(i) 'sets workbook name to be opened
Set WB = Workbooks.Open(fName) 'opens the file
With WB
Set Wks = Nothing
For Each Wks In WB.Worksheets
If LCase(Wks.CodeName) = LCase("sheet1") Then
Exit For
End If
Next Wks

If Wks Is Nothing Then
MsgBox "no sheet with a codename Sheet1 in: " _
& vbLf & WB.FullName
Else
Wks.Range("B10").Formula = "=IF(E5=0,0,E5/C10/C13)"
End If

.Close savechanges:=Not (Wks Is Nothing)
End With
Next
End If
End With

Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub





jlclyde wrote:

Here is the macro that I have and have used to change a test folder
with test workbooks. Not all of the sheets have the same same but
they are all sheet1. Can you help me amend this to have it go to
sheet1 of each workbook?

Thanks,
Jay

Sub changeFormulas()
Dim WB As Workbook
Dim fs
Dim i As Integer

Application.DisplayAlerts = False
Application.ScreenUpdating = False

Set fs = Application.FileSearch

With fs
.LookIn = "C:\Documents and Settings\JLClyde\Desktop\Steve" '
change this to the folder you want
.Filename = "*.xls"
If .Execute 0 Then ' checks to see if there are excel files
in the folder specified
For i = 1 To .FoundFiles.Count ' for each file found
fname = .FoundFiles(i) 'sets workbook name to be
opened
Set WB = Workbooks.Open(fname) 'opens the file
With WB
Sheet1.Range("B10") = "=IF(E5=0,0,E5/C10/C13)"
.Close SaveChanges:=True
End With
Next
End If
End With

Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

--

Dave Peterson- Hide quoted text -

- Show quoted text -

Dave,
It is the one that is the left most positoin. I was going around
with worksheets, sheet, sheets. Thank you very much this will do the
trick nicely.
Jay

--

Dave Peterson