Store current workbook name in variable and use it

Hi there to everyone, this here is my first post after my introduction post.

I will explain what I am trying to do, basically I need a macro (name of the macro: ImportData) in my current opened workbook ("MyCurrentWorkboook.xlsm") that does following in chronological order;

1. Open a database file (workbook "DATABASE.xlsm")
2. Copy a range from a sheet in that workbook
3. Go back to my current opened workbook ("MyCurrentWorkboook.xlsm")
4. Paste the range in a range I need
5. Close the databasefile

Nothing too difficult one might think.

Please Login or Register  to view this content.

I got the code working but only when I hardcode the filenames. The databasefile is not a problem for it is a static document, always. But I don't know the name of my current opened workbook, the file itself gets copied over and over again so the name changes depending on the project. So how do I cange the code in order it uses the current workbookname?

This is where it becomes a drama:

Please Login or Register  to view this content.

Any help on this one is much appreciated thanks in advance!

Originally Posted by rorya

ThisWorkbook will always refer to the workbook with the code in it, so you can use:

Please Login or Register  to view this content.

Thank you very much Rorya, this works like a charm!
The with loop is a method I would have not come up with.
I am sure this will be useful in future coding as well.

WideBoyDixon: thank you as well.
I am sure that your code works great too but I would have had a second question, how to shorten this part:

Please Login or Register  to view this content.

The with loop just does that where your solution still "doubles" the paste action.
My preference goes to the other solution.

Again, thanks both of you for the quik replies!