Extracting data from file in directory and extracting filename

[brad999]

Hi all,

I have a directory folder with an active workbook and another workbook id like to copy data from. The Following macro opens up a file in the same directory, copies some data and pastes it in the active workbook. However with this code I have to specify the filename, 'Data.xlsm' in the example code. I would like it to copy data from the only other workbook in the current directory WITHOUT having to specify the name in the code, so just opening it up no matter what filename it has.

In addition I would like to extract the filename from the workbook im copying data from and paste it into the activewoorkbook in sheet 1 Cell A1. I had a look at getopenfilename function but cant seem to make it work for my purpose.

Thanks very much!

Please Login or Register  to view this content.

[jindon]

Do you mean like this?

Please Login or Register  to view this content.

[brad999]

Yes jindin! Thank you, however just a couple things... I would like the data to be pasted as values rather than just a normal paste.

Also after selecting the file to copy data from, it seems to take about 20 seconds to complete, whereas the code I posted takes about 5. Any idea why this is, and if it can be quicker?

Thanks!

[jindon]

The difference between yours and mine is only:

Please Login or Register  to view this content.

So it should not make any difference in execution time....

If you want to copy values then change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

[brad999]

Excellent! Ah yeah it turns out pasting as values is much quicker! Thanks very much jindon