Converting a Variable Filename to a Constant Filename

Hello Fellow Excel Users!

I have a macro that pastes data into a file, say File A, from all files in a
specific folder. The macro opens each file in the folder, pastes data into
File A, and then closes the file.

The structure of the macro is something like:

(for each file in the folder)
If objFile.Name <> ActiveWorkbook.Name Then (as File A is inside the folder)
Worksheets("sheet1").Activate
Range("A1:E50").Select
Selection.Copy
Windows("File A.xls").Activate
Worksheets("sheet1").Select
Range("A1").Select
Selection.Paste
(close file)

In this code, everytime the name of File A changes, I would have to change
the line in the code that references file A ("Windows("File
A.xls").Activate"), and I cant use "ActiveWorkbook.Name" to refer to File A
because the ActiveWorkbook changes once the other files are opened. Is it
possible to lock in the ActiveWorkbook.Name so that once the other files are
open it still refers to Filea A?

Any assistance would be greatly appreciated.

Thank you for your help!

Magnivy

On Tue, 15 Aug 2006 07:18:02 -0700, Magnivy
<[email protected]> wrote:

>Hello Fellow Excel Users!
>
>I have a macro that pastes data into a file, say File A, from all files in a
>specific folder. The macro opens each file in the folder, pastes data into
>File A, and then closes the file.
>
>The structure of the macro is something like:
>
>(for each file in the folder)
>If objFile.Name <> ActiveWorkbook.Name Then (as File A is inside the folder)
>Worksheets("sheet1").Activate
>Range("A1:E50").Select
>Selection.Copy
>Windows("File A.xls").Activate
>Worksheets("sheet1").Select
>Range("A1").Select
>Selection.Paste
>(close file)
>
>In this code, everytime the name of File A changes, I would have to change
>the line in the code that references file A ("Windows("File
>A.xls").Activate"), and I cant use "ActiveWorkbook.Name" to refer to File A
>because the ActiveWorkbook changes once the other files are opened. Is it
>possible to lock in the ActiveWorkbook.Name so that once the other files are
>open it still refers to Filea A?

When FileA .xls is active,

Set bk = ActiveWorkbook

Do your stuff. Then to get back to FileA.xls,

bk.Activate

Thanks John!

"John Cadagin" wrote:

> On Tue, 15 Aug 2006 07:18:02 -0700, Magnivy
> <[email protected]> wrote:
>
> >Hello Fellow Excel Users!
> >
> >I have a macro that pastes data into a file, say File A, from all files in a
> >specific folder. The macro opens each file in the folder, pastes data into
> >File A, and then closes the file.
> >
> >The structure of the macro is something like:
> >
> >(for each file in the folder)
> >If objFile.Name <> ActiveWorkbook.Name Then (as File A is inside the folder)
> >Worksheets("sheet1").Activate
> >Range("A1:E50").Select
> >Selection.Copy
> >Windows("File A.xls").Activate
> >Worksheets("sheet1").Select
> >Range("A1").Select
> >Selection.Paste
> >(close file)
> >
> >In this code, everytime the name of File A changes, I would have to change
> >the line in the code that references file A ("Windows("File
> >A.xls").Activate"), and I cant use "ActiveWorkbook.Name" to refer to File A
> >because the ActiveWorkbook changes once the other files are opened. Is it
> >possible to lock in the ActiveWorkbook.Name so that once the other files are
> >open it still refers to Filea A?
>
> When FileA .xls is active,
>
> Set bk = ActiveWorkbook
>
> Do your stuff. Then to get back to FileA.xls,
>
> bk.Activate
>
>