Alternate Between Excel Files

[Hamyatta]

Hi All,

Can anyone please show me how to alternate between two or three excel files using VBA...

For instance
Open Files File A, File B, File C, and File D THEN
copy from File A, then paste into File X, then close File A
copy from File B, then paste into File X, then close File B
copy from File C, then paste into File X, then close File C
copy from File D, then paste into File X, then close File D

Best,
AY

 

[PHV]

The macro recorder is your best friend for learning.

Note: why open all the files at the same time ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Hamyatta]

Hey PHV,

Thanks in advance!!

I have to open all of them at the same time as there over 50 files with different names...

Regards,
H

 

[Hamyatta]

PHV,

I have tried few codes but this is quite a challenge..

I have to open all of them at the same time as there over 50 files with different names...
Then copy respective cells (8 cells, one at the time), paste it into an existing excel file, then close that source file and move to the next....

Cheers,
HY

 

[AnotherHiggins]

->I have to open all of them at the same time as there over 50 files with different names...

I still don't get it. Why open 50 files at once? That's likely to overwhelm your processor.

Why not cycle through the files one at a time? Keep you master file open, open each one of the source files, copy what you need, then close it.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[Hamyatta]

John,

This sounds pretty good to me...
The challenge lies in "cycling" through the files one by one... because all the files have different names...

Any idea on how one can write code to cycle through n files with different names..? Thanks

Cheers

 

[PHV]

How do you expect the code knows which files to process ?
Tip: have a look at the Dir function or the FileSearch object.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[AnotherHiggins]

With your master sheet open, use something like this:

strOrigFile = ActiveWorkbook.name

Dim fs
Set fs = Application.FileSearch

With fs
    .LookIn = MyPath
    .Filename = "*.xls"
    .Execute
        For i = 1 To .FoundFiles.Count
            Workbooks.Open .FoundFiles(i)
            strCurrentWBName = ActiveWorkbook.name
                'copy and paste whatever here
            Windows(strCurrentWBName).Close
        Next i
End With

That will open and close each file in a single folder.

As PH pointed out, you can use your macro recorder to get a start for that goes in the middle there.

Tools > Macro > Record New Macro

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.