Paste filename into spreadsheet

[Duncgee]

I have a number of workbooks from which a macro copies data. This data is then pasted into another workbook. How can I have the macro paste the filenames as well as the data.

 

[PaddyD]

On 2002-10-08 20:21, Duncgee wrote:
I have a number of workbooks from which a macro copies data. This data is then pasted into another workbook. How can I have the macro paste the filenames as well as the data.

Hi - welcome to the board!
Care to give a bit more info about where the filenames should go!!

Paddy

 

[Duncgee]

From E2 down to match the data in cols A to D

 

[RichardS]

What if you got your Macro to place the

=CELL("filename")

formula in a blank cell in the source workbook before copying to the destination, then copy that as a value to the destination workbook. That will give the complete filename, including path and sheet name. If you only want the filename, replace with

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

HTH

Richard