Converting a Variable Filename to a Constant Filename

G

Guest

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
 
J

John Cadagin

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?
Click to expand...

When FileA .xls is active,

Set bk = ActiveWorkbook

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

bk.Activate
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Get Filename without the extension 9
macro required.. 1
Filesearch in 2007 1
Variable Filename 1
Automation Error. What is this? 2
Filename str cut too short 7
Unable to change ActiveSheet within Workbook_Open 2
Problem with filename =Heute() 13

Top