Excel Tip: Automating the Filepath on a Spreadsheet | AccountingWEB

Excel Tip: Automating the Filepath on a Spreadsheet

On the surface it would seem that entering the name of your path and file in the footer (or header) of an Excel worksheet is not an automatic operation. This is such a frequently-requested task, you would think that there would be a quick menu choice to perform this duty. But there is not. In the absence of such a menu selection, here is a quick little macro that you can place in your worksheet that should do the trick.

  1. Choose Tools, Macro, Macros from the Excel menu.

  2. Give the macro a name, such as Path or addfilename, then select PERSONAL.XLS as the location for this macro (by placing the macro in the Personal workbook, the macro will be available to all other workbooks).

  3. Click the Create button to open the visual basic editor where your macro code will be entered. In the Module area on the right side of your screen, between the Sub and the End Sub lines of this macro, enter the following code, all on one line:

  4. ActiveSheet.PageSetup.RightFooter = ActiveWorkbook.Path & "\" & ActiveWorkbook.Name

  5. Close the visual basic window by clicking the "x" in the upper right corner. From now on, whenever you wish to place the path and file name in your footer, choose Tools, Macro, Macros, and click on the name of this macro, then click the Run button, and it will appear automatically!
Wait, there's more!
There's always more at AccountingWEB. We're an active community of financial professionals and journalists who strive to bring you valuable content every day. If you'd like, let us know your interests and we'll send you a few articles every week either in taxation, practice excellence, or just our most popular stories from that week. It's free to sign up and to be a part of our community.
Premium content is currently locked

Editor's Choice

WHAT KIND OF FIRM ARE YOU?
As part of our continued effort to provide valuable resources and insight to our subscribers, we're conducting this brief survey to learn more about your personal experiences in the accounting profession. We will be giving away five $50 Amazon gift cards, and a $250 Amazon gift card to one lucky participant.
This is strictly for internal use and data will not be sold
or shared with any third parties.