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
> 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
[quoted text clipped - 35 lines]
> Application.DisplayAlerts = True
> End Sub

Signature
Dave Peterson
jlclyde - 24 Mar 2008 22:25 GMT
> 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?
[quoted text clipped - 97 lines]
>
> - 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 - 24 Mar 2008 22:33 GMT
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.
> > 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?
[quoted text clipped - 103 lines]
> trick nicely.
> Jay

Signature
Dave Peterson