Macro to paste in the active cell the contents of a cell from another file??

[LarryB]

Hello guys,

I will appreciate very much if you can help me with the following
situation:

I have about 120 Excel files and one other file, let's call it the
Source File.

I need to copy the contents of one cell from the Source file into each
one of the 120 files and also do the same with a text box.

It will be necessary anyways to go manually through each of the 120
files and check them so I was trying to make a macro that will paste
into the active cell the contents of, let's say, cell B40 from the
Source file. Then I was planning to do another macro for the text box,
ie to paste in the active cell (which will be a different one) the
textbox from the Source file. (I will click manually the active cell).

Because of the two (or may be three) things that need to be pasted in
each of the 120 files I can't just paste the clipboard.

So I was trying to do this macro using the recorder (I know nothing
about code) The problem is that it works only for the file it's
created in. If run in another file it gives an error (Runtime error 9:
Subscript out of range) and stops on the 5-th line that has the name of
the file it was created in("CO23df_pds.xls"). Here is the code:

Sub Macro6()
'
' Macro6 Macro
' Macro recorded 6/9/2006 by LARRYB
'

'
Windows("Source File.xls").Activate
Range("B40").Select
Application.CutCopyMode = False
Selection.Copy
Windows("CO23df_pds.xls").Activate
ActiveCell.Select
ActiveSheet.Paste
End Sub


It looks to me it's not pasting in the currently active cell but is
trying to paste in the file it was created in.

So as I have 120 of these, my question is how to make a macro that
pastes in the active cell the contents of a given cell from another
file (in my case B40 from Source file)???

Thank you in advance for all help - it is really appreciated !! Also
please explain in a simple way as I've never worked with any code...
)

Regards,
Larry B

 

[Ron de Bruin]

Hi LarryB

Start here
http://www.rondebruin.nl/copy4.htm

 

[LarryB]

Hi Ron !
Thank you for this link but I can' t understand anything...... That's
too advnced for me (

All I need is a macro to paste in the active cell the contents of a
given cell from another file.

I believe this should be shorter than 10 lines of code....

Regards,
Larry

 

[Ron de Bruin]

You say :

I have about 120 Excel files and one other file, let's call it the
Source File.

I need to copy the contents of one cell from the Source file into each
one of the 120 file

The macro is doing that
http://www.rondebruin.nl/copy4.htm

Use this example to change a cell in all workbooks in the folder C:\Data
http://www.rondebruin.nl/copy4.htm#cell

I don't think there is a easier way