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