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, an
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 i
located in could have various different filenames, and when I've bee
testing recording macros, the filename is being recorded in the macr
due to the switching between workbooks.

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


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


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

Cheers,

Barella :confused

--
barell
-----------------------------------------------------------------------
barella's Profile: http://www.excelforum.com/member.php...fo&userid=1550
View this thread: http://www.excelforum.com/showthread.php?threadid=27078