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

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

While a variable can be supplied as the argument to workbooks collection, if
there are multiple workbooks open, how would the macro know which one you
wanted to copy to?

As your formula shows, a workbook has no problem knowing its own name, but a
macro would have to know how to determine which workbook is the destination
workbook if you don't supply a workbook name.

--
Regards,
Tom Ogilvy

"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