Barella,

You can get the filename of the original workbook simply by:

OrigFileName = ActiveWorkbook.Name

HTH,
Nikos

"barella" wrote in message
...

I'm after some advice and was wondering if anyone out there could help.

I'm needing to write a macro that selects a sheet in a workbook, and
then copies and pastes the values into a new sheet. OK, so far.

But here's where the problem comes. The file the orginal sheet is
located in could have various different filenames, and when I've been
testing recording macros, the filename is being recorded in the macro
due to the switching between workbooks.

I have found a formula which shows the filename on the worksheet, but
don't know how to feed this information into VBA.


Code:
--------------------

=MID(CELL("filename",A1),FIND("[",CELL("filename",A1))+1,FIND("]",CELL("file
name",A1))-FIND("[",CELL("filename",A1))-1)
--------------------


Is this the right way of going about this, or is there a better, or
easier way of doing it?

Cheers,

Barella


--
barella
------------------------------------------------------------------------
barella's Profile:
http://www.excelforum.com/member.php...o&userid=15506
View this thread: http://www.excelforum.com/showthread...hreadid=270782