Referencing active workbook file name in code automatically.

Good morning. I'm not sure my title captures what I'm shooting for so if I can give this thread a more accurate title please chime in.

The following line of code works great for automatically copying information from one workbook into a brand new workbook. It works perfectly as long as I manually enter the correct file name+extension of the workbook I'm copying from.

Please Login or Register  to view this content.

I would like to use this line of code in a form that my users will be opening, saving, closing, opening, re-saving as a different file name, closing......you get the picture. My users will not be able to go into the code and change the file name every time the file name changes. Is there a way to write the code so it will find the file name automatically?

Please Login or Register  to view this content.

I've attached a mock workbook for review. Thanks.

Matthew

Why not?

Please Login or Register  to view this content.

John,

That works brilliantly! Thanks. BTW, I've tried (and failed up to this point) to automatically "deselect" the range that's copied in the originating Sheet and I can't seem to get the code correct. Thoughts?

Thanks again.

Matthew

What do you mean by deselect? What is you ultimate goal? Would selection.copy be an option?

Originally Posted by JOHN H. DAVIS

What do you mean by deselect?

When I return to the workbook, where my data was just copied from, the range is still bordered by the "blinking" border. I can hit Esc and it goes away but I was hoping to add a line of code to do this automatically.

I added a line of code and it seems to be working...I need to test it more to make sure it's solid.

Please Login or Register  to view this content.

Just for curiosity's sake, what would you have suggested to address the issue? Thanks.

Matthew

No you hit it on the nail. I just wasn't sure of what you meant by deselect.

John,

Thank.

Matthew

You're welcome. Glad to help out.