Multiple Workbook Macro

[matman]

Does anybody know of a simple code that will open all files in a directory and then copy the same range from each workbook and paste each range into another workbook that has existing worksheets as the same name as the workbook. The existing worksheets have an area specified for the range to be pasted into.

Many thanks

[bergy]

On 2002-05-14 07:29, matman wrote:
Does anybody know of a simple code that will open all files in a directory and then copy the same range from each workbook and paste each range into another workbook that has existing worksheets as the same name as the workbook. The existing worksheets have an area specified for the range to be pasted into.

Many thanks

Simple code? Not really. it's not hard code, but it is time consuming.

Here's some code for the first part. I took it right from the help files and did a slight modification.

Sub test2()
Set fs = Application.FileSearch
With fs
.LookIn = "C:My Documents"
.FileName = "*.xls"
If .Execute > 0 Then
MsgBox "There were " & .FoundFiles.Count & _
" file(s) found."
For i = 1 To .FoundFiles.Count
Workbooks.Open i
Next i
Else
MsgBox "There were no files found."
End If
End With
End Sub


Maybe you could try recording a macro for the rest and see if it works.

[Audiojoe]

http://www.mrexcel.com/board/viewtop...c=6304&forum=2

Howdy, you can tailor this to your needs. Very clever bit of code...AJ is the man

Audiojoe

[ This Message was edited by: Audiojoe on 2002-05-14 07:53 ]

[matman]

Thanks AJ!