way/macro to put file path + tab name into header/footer?

[maxmadx]

Is it possible to either have a macro that you can assign to a button, or another way to automatically populate the header/footer with the file path/name where the spreadsheet is located, and the name of the sheet tab?

Thanks,

STeve

 

[jim may]

Natively, this is done in Excel by going to File, Page Setup, Header/Footer Tab choosing header, L,C,R, and Click on the Icons provided for Path Sheet, etc.

If you know how to do this manually, then why not try RECORDING a Macro to give you a
strat at this?

 

[Weaver]

Try:

Sub setfooter()
    Application.ScreenUpdating = False
    
    With ActiveWorkbook
    
        For x = 1 To .Worksheets.Count
            With .Worksheets(x).PageSetup
                .LeftFooter = "[&A] &Z&F - &D"
                .CenterFooter = ""
                .RightFooter = ""
                '.Orientation = xlLandscape
                '.FitToPagesWide = 1
            End With
        Next x
        
        For x = 1 To .Charts.Count
            With .Charts(x).PageSetup
                .LeftFooter = "[&A] &Z&F - &D"
                .CenterFooter = ""
                .RightFooter = ""
                '.Orientation = xlLandscape
                '.FitToPagesWide = 1
            End With
        Next x
        
    End With
    
    Application.ScreenUpdating = True
    
End Sub

 

[Weaver]

This was something I'd already written, so it might not fit your bill exactly. Hopefully you'll be able to figure how to change it to suit.

 

[shyrath]

Is it possible to either have a macro that you can assign to a button, or another way to automatically populate the header/footer with the file path/name where the spreadsheet is located, and the name of the sheet tab?

Thanks,

STeve

Put this in A1, or wherever you wish to put the filename.

=CELL("filename")

If you want to populate this on open of the worksheet.

 

[maxmadx]

Thats awesome thank you!

Is it possible to add a button to your toolbar that will always be there no matter which spreadhseet you open? That will help to run this macro everytime we need it.

Cheers

 

[maxmadx]

I used this macro, it works really good btw, thanks to both for replying. Can you help with my other query?

 

[Weaver]

Click tools -> customise and select the commands tab
In categories, select macros
You can either select the custom menu item or, probably better for your usage, the custom button
Drag this into the toolbar
Then right click the icon for the context menu.
This will allow you to redesign the icon, give it a name and most importantly link it to a macro.
BUT - the macro should be in your personal.xls workbook for it to work in all sheets opened - you may have already done this.
If not, and you're not already using the personal workbook, the quickest way is to record a new macro and choose to store it in the personal macro workbook. It doesn't matter what you do, just copy and paste a few cells or something.

Hope that helps!

 

[forensics1]

File name as header or footer for hundreds of excel and word docs?

I am glad I found this thread. My problem is similar. I do computer forensic, accounting, and legal work. I have to print out thousands of excel and word files WITH the file name printed (as either a footer or header). The reason I need to print the file names is for legal purposes, I need to identity the file name of the document.

When I am printing out hundreds of documents at a time, or even let's say 20-30 at a time, I have no way of knowing the title of the document printed out. If I printed one or two document at a time, then yes, I would know the file name.

How do I get every file and document to print with its file name? I am talking about documents already created. I have organized and separated the documents by file type (one folder for all word docs, one folder for all excel docs, etc.)

I found an interim solution with Microsoft Word- -and that is to print a "cover sheet" of the document properties (which contains the file name). I then have to manually write the file name on the printed document.

Some people have suggested that I use a macro, file scripting to cycle through files in a folder and apply a footer of &[file] automatically. How do I do this?