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!

You may like these other stories...

By Jason Bramwell When CPA Barry MacQuarrie started the SocialCPAs Social Media Survey in 2010, he said social networking was still relatively new to the accounting profession. The self-proclaimed "social media...
By Jason Bramwell In an effort to address the increased demand for comprehensive and intensive training on eXtensible Business Reporting Language (XBRL), the American Institute of CPAs (AICPA) and XBRL US Inc., the not-...
By Lauren Prosser, Director of ProfitCents Professional Services, Sageworks Staffing is among the top management issues facing CPA firms, according to a recent survey by Sageworks, a financial information company. That...

Upcoming CPE Webinars

Jul 16
Hand off work to others with finesse and success. Kristen Rampe, CPA will share how to ensure delegated work is properly handled from start to finish in this content-rich one hour webinar.
Jul 17
This webcast will cover the preparation of the statement of cash flows and focus on accounting and disclosure policies for other important issues described below.
Jul 23
We can’t deny a great divide exists between the expectations and workplace needs of Baby Boomers and Millennials. To create thriving organizational performance, we need to shift the way in which we groom future leaders.
Jul 24
In this presentation Excel expert David Ringstrom, CPA revisits the Excel feature you should be using, but probably aren't. The Table feature offers the ability to both boost the integrity of your spreadsheets, but reduce maintenance as well.