Hello!

I'm new here, but I'd immediately like to take advantage of you
superior knowledge of all things Excel ;-) I'm currently doing researc
for school, which has culminated in a model. This model is a workbook
containing some 10 worksheets. Now I have entered varios scenarios i
the model, and saved all the iterations.

This means that I have some 120 workbooks with the same structure.

Every workbook has a certain value (an efficiency) denoted in a cel
(for each workbook, this value is identical, as well as in the 'same
cell). This value is 70%. Now it occurs to me that this should b
60%.

Ofcourse I could simply open the all the workbooks (the 120 version o
the model...) and change the value of cell C5 on worksheet X from 70 t
60 one hundred and twenty times over, but I was wondering if there i
an easier way to go about this, with macros for example (especiall
since I probably need to overwrite some other values as well, late
on).

In short: is there an easy way to have Excel overwrite a constant in
given cell with a new, constant value for, say, every workbook in
folder (thus changing cell C5, which now has 70% in it, to, say, 60
for every workbook in that folder)?


Thanks for your time! Forums like these are what make the interne
great.

Bram Verhees

P.S. I apologise if not all the terminology is correct. Being Dutch
I'm using a Dutch version of Excel, so I had to wing it here and there

--
BR4
-----------------------------------------------------------------------
BR4M's Profile: http://www.excelforum.com/member.php...fo&userid=1682
View this thread: http://www.excelforum.com/showthread.php?threadid=32016

Hi BR

With all files in the Folder C:\Data for example
It will change cell A1 of the first sheet of the workbook
mybook.Worksheets(1).Range("a1").Value = 70

You can also use
mybook.Worksheets("yoursheetname").Range("a1").Val ue = 70




Sub Copyrange1()
Dim mybook As Workbook
Dim FNames As String
Dim MyPath As String
Dim SaveDriveDir As String

SaveDriveDir = CurDir
MyPath = "C:\Data"
ChDrive MyPath
ChDir MyPath
FNames = Dir("*.xls")
If Len(FNames) = 0 Then
MsgBox "No files in the Directory"
ChDrive SaveDriveDir
ChDir SaveDriveDir
Exit Sub
End If

Application.ScreenUpdating = False
Do While FNames < ""
Set mybook = Workbooks.Open(FNames)
mybook.Worksheets(1).Range("a1").Value = 70
mybook.Close True
FNames = Dir()
Loop
ChDrive SaveDriveDir
ChDir SaveDriveDir
Application.ScreenUpdating = True
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl


"BR4M" wrote in message ...

Hello!

I'm new here, but I'd immediately like to take advantage of your
superior knowledge of all things Excel ;-) I'm currently doing research
for school, which has culminated in a model. This model is a workbook,
containing some 10 worksheets. Now I have entered varios scenarios in
the model, and saved all the iterations.

This means that I have some 120 workbooks with the same structure.

Every workbook has a certain value (an efficiency) denoted in a cell
(for each workbook, this value is identical, as well as in the 'same'
cell). This value is 70%. Now it occurs to me that this should be
60%.

Ofcourse I could simply open the all the workbooks (the 120 version of
the model...) and change the value of cell C5 on worksheet X from 70 to
60 one hundred and twenty times over, but I was wondering if there is
an easier way to go about this, with macros for example (especially
since I probably need to overwrite some other values as well, later
on).

In short: is there an easy way to have Excel overwrite a constant in a
given cell with a new, constant value for, say, every workbook in a
folder (thus changing cell C5, which now has 70% in it, to, say, 60%
for every workbook in that folder)?


Thanks for your time! Forums like these are what make the internet
great.

Bram Verhees

P.S. I apologise if not all the terminology is correct. Being Dutch,
I'm using a Dutch version of Excel, so I had to wing it here and there.


--
BR4M
------------------------------------------------------------------------
BR4M's Profile: http://www.excelforum.com/member.php...o&userid=16822
View this thread: http://www.excelforum.com/showthread...hreadid=320168

This code will open every workbook in the "C:\Test" directory
individually,
and for every sheet in the workbook, change the c5 value to 0.6.
The macro will save and close each workbook.

Sub searchFolder()
Application.ScreenUpdating=False
Set fs = Application.FileSearch
With fs
..LookIn = "C:\Test"
..Filename = "*.xls"
If .Execute 0 Then
Else
MsgBox "There were no files found."
Exit Sub
End If
Dim ModifyBook As Object
For i = 1 To .FoundFiles.Count
Set ModifyBook = Workbooks.Open(.FoundFiles(i))

For Each c In ModifyBook.Sheets
' put your replace code here


c.Cells(3, 5).Value = 0.6 ' Changes the cell c5's value to 0.6 on
every sheet
Next c

ModifyBook.Save
ModifyBook.Close
Next i
End With
Application.ScreenUpdating=True
End Sub


--
crispbd
------------------------------------------------------------------------
crispbd's Profile: http://www.excelforum.com/member.php...o&userid=10880
View this thread: http://www.excelforum.com/showthread...hreadid=320168

Thanks so much guys! I tried both macro's, and both worked perfectly
You have no idea how much time you guys saved me. Also, this actuall
makes it possible to do a sensitivity analysis of sorts, since I ca
now more or less change 'set' values at will over the entire range o
workbooks.

I almost can't believe you guys helped out a newbie like me so fast an
so effectively. This board truly sets the standard.

Sincerely,

Bram Verhee

--
BR4
-----------------------------------------------------------------------
BR4M's Profile: http://www.excelforum.com/member.php...fo&userid=1682
View this thread: http://www.excelforum.com/showthread.php?threadid=32016

Hi Bram

Note: Application.FileSearch is not always working correct that's why
I use Dir.
If you search the archives you will find a lot of threads about problems with it.




--
Regards Ron de Bruin
http://www.rondebruin.nl


"BR4M" wrote in message ...

Thanks so much guys! I tried both macro's, and both worked perfectly.
You have no idea how much time you guys saved me. Also, this actually
makes it possible to do a sensitivity analysis of sorts, since I can
now more or less change 'set' values at will over the entire range of
workbooks.

I almost can't believe you guys helped out a newbie like me so fast and
so effectively. This board truly sets the standard.

Sincerely,

Bram Verhees


--
BR4M
------------------------------------------------------------------------
BR4M's Profile: http://www.excelforum.com/member.php...o&userid=16822
View this thread: http://www.excelforum.com/showthread...hreadid=320168

Whoops. Another slight problem. While the macro itself works perfectly,
every time it opens one of my workbooks Excel asks if it needs to
update the links present in that workbook. I don't want it to do this,
but to accomplish this I still have to click on 'no' for every workbook
the macro opens. While this is definitely less work than it was
originally, I'd still like to automate this process.

It there a way to tell the macro above (the first one) to not update
the links? Or, perhaps even easier, is there a way to (temporarily)
stop Excel from asking me that question and just not update?

Thanks in advance,

Bram Verhees


--
BR4M
------------------------------------------------------------------------
BR4M's Profile: http://www.excelforum.com/member.php...o&userid=16822
View this thread: http://www.excelforum.com/showthread...hreadid=320168

Hi BR4M

See the Tip on this page
http://www.rondebruin.nl/copy4.htm

--
Regards Ron de Bruin
http://www.rondebruin.nl


"BR4M" wrote in message ...

Whoops. Another slight problem. While the macro itself works perfectly,
every time it opens one of my workbooks Excel asks if it needs to
update the links present in that workbook. I don't want it to do this,
but to accomplish this I still have to click on 'no' for every workbook
the macro opens. While this is definitely less work than it was
originally, I'd still like to automate this process.

It there a way to tell the macro above (the first one) to not update
the links? Or, perhaps even easier, is there a way to (temporarily)
stop Excel from asking me that question and just not update?

Thanks in advance,

Bram Verhees


--
BR4M
------------------------------------------------------------------------
BR4M's Profile: http://www.excelforum.com/member.php...o&userid=16822
View this thread: http://www.excelforum.com/showthread...hreadid=320168

Hah! Awesome. That certainly did the trick. Thanks again, Ron. You
assistance has been quite valuable!

Cheers,

- Bra

--
BR4
-----------------------------------------------------------------------
BR4M's Profile: http://www.excelforum.com/member.php...fo&userid=1682
View this thread: http://www.excelforum.com/showthread.php?threadid=32016

You are welcome

--
Regards Ron de Bruin
http://www.rondebruin.nl


"BR4M" wrote in message ...

Hah! Awesome. That certainly did the trick. Thanks again, Ron. Your
assistance has been quite valuable!

Cheers,

- Bram


--
BR4M
------------------------------------------------------------------------
BR4M's Profile: http://www.excelforum.com/member.php...o&userid=16822
View this thread: http://www.excelforum.com/showthread...hreadid=320168