I would like to delete unused rows and insert the filename in column Z of several spreadsheets. Each spreadsheet has a title so I would like to insert the title "Original File" in column Z followed by the filename in the used range for col Z. For each spreadsheet, I would also like to delete unused rows. Somewhere in here, I think I need to determine the last used row (even if there are blank rows) in order to insert the filename in the appropriate range. Each file has 1 worksheet. Can you help me put this together?

The macro recorder gave me this piece of code:

Range("R2").Select
ActiveCell.FormulaR1C1 = "Original File"
    Range("R3").Select
    ActiveCell.FormulaR1C1 = "=MID(CELL(""filename""),SEARCH(""["",CELL(""filename""))+1,SEARCH(""]"",CELL(""filename""))-SEARCH(""["",CELL(""filename""))-1)"

Selection.Copy
Range("R4:R28").Select
ActiveSheet.Paste

I was able to gather this code to loop through the file names in the directory:

For Each vafilename In .FoundFiles
  updatefiles (vafilename)
Next vafilename

Thanks for taking the time to help.
nori233

Dear Nori 233

In my opinion, I will use Workbook_BeforePrint Event. Do as the follow step

1 Open File and Activate the Visual Basic Editor (Press Alt+F11)
2 Goto Project Explorer window (Press Ctrl+R)
3 Double Click Thisworkbook Icon
4 Click Left Combobox On Coding Window Choose Workbook
5 Click Right Combobox On Coding Window Choose BeforePrint
6 Copy the follow code then paste inside the procedure

dim sht As Worksheet
For each sht In thisworkbook.Worksheets
 sht.pagesetup.leftfooter = thisworkbook.name
Next

Hope it will be helpful information for you.

N. Yauvasuta