Rename cell based on file name for multiple files

Hi,
I'm looking for a macro that can rename cell H1 (sheet1) to a portion of the file name, and I need this to loop for every file in that directory.

Any help is greatly appreciated!

To elaborate a bit on this request, I have a macro that pulls a single column from multiple workbooks and pastes it into a single workbook. However, it does this is no particular order and the header of the column it is pulling has the same name in each file. So I'm trying to have that column's header changed to the file's name so I can identify it once it is pasted into main workbook. Maybe the rename function can be added to the macro that pulls the columns? See below:

Please Login or Register  to view this content.

Thanks!