I need to write a macro that will consolidate data from a bunch of different
files, but the files are pretty badly formatted, so I want to insert the
original filename into an extra column before I copy the data out of the
spreadsheet and paste it into the new, large spreadsheet. I was going to just
use the Cell function to get the filename, then paste values so it didn't
change when I pasted them into the new spreadsheet, but that produces errors
in my code.

Any suggestions?