Excel Novice, Stupid Question, but help!
Thank you!
-Steve
JMB wrote:
Here is an outline that may get you started. You will need to change the
following:
.LookIn = "Your folder's path" - I would copy all of the workbooks to a
separate folder
ActiveSheet.Range("A10").Formula = _
"=SUM(A1:A9)"
This puts a simple sum formula in cell A10 of the active sheet. If you want
a sheet other than the active sheet use
Worksheets("Your Worksheet Name").Range("YourRange").Formula = "YourFormula"
You'll need to include a statement like this for each formula you want
changed.
Be sure you have backups of your data before testing.
Sub Test()
With Application.FileSearch
.NewSearch
.LookIn = "I:\Test"
.SearchSubFolders = False
.Filename = "*.xls"
.MatchTextExactly = True
.FileType = msoFileTypeAllFiles
.Execute
For i = 1 To .FoundFiles.Count
Workbooks.Open Filename:=.FoundFiles(i), _
UpdateLinks:=0
ActiveSheet.Range("A10").Formula = _
"=SUM(A1:A9)"
Workbooks(ActiveWorkbook.Name).Close savechanges:=True
Next i
End With
End Sub
" wrote:
Are all 200 sheets in the same workbook?
- no, each sheet is in a seperate workbook.
Are the formulas the same from one
sheet to the next?
- the formulas are the same from one *workbook* to the next
Are the formulas located in the same cell(s) from on
sheet to the next?
- the formulas are located in the same cell(s) from one *workbook* to
the next.
What are the formulas now, and what should they be?
- some are very long with a lot of if-testing.
We generate all the spreadsheets from a single template, that is why
the formulas are the same across workbooks and located in the same
place in each workbook. We could just change the template and re-create
all the spreadsheets but then our data-entry people would have to re-do
their data entry for the 200 spreadsheets.
Thanks for your help on this so far, I really appreciate it.
|