Macro Use after File Name is Changed

[Hoota Caroota]

I use Excel 2003. I've written some macros for a workbook, BookA, that will be opened then saved, many times and with many other names. The problem is that the macros transfer data to and from another workbook, BookB. The name BookB will never be changed. Here's part of the code that's giving me problems:

Please Login or Register  to view this content.

How can I use the macro if I open BookA.xls then change the name to BookZ.xls for my data entries? At the same time BookZ is open, I may use another instance of BookA.xls and save that one as BookY.xls. What's the most feasible way to still use the macros that I've created in BookA? Can it even be done?

[Hoota Caroota]

So how 'bout this?

What if, when I open BookA, I save it as BookZ where I enter all the data. Then, when the project is complete, I run a macro that changes the name back to BookA, runs the macro which transfers data to BookB, then changes the name back again to BookZ.

If I do that, it seems the macros will all run as planned. Correct?

I still need help with the code.

[shg]

Hard to guess; maybe like this:

Please Login or Register  to view this content.

[broro183]

As Shg says, it is hard to guess but here's another try using Shg's approach...

Please Login or Register  to view this content.

hth
Rob