Exporting to a new workbook

[barella]

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

 

[Nikos Yannacopoulos]

Barella,

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

OrigFileName = ActiveWorkbook.Name

HTH,
Nikos

 

[Tom Ogilvy]

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.