+ Reply to Thread
Results 1 to 2 of 2

Inserting new column, hiding oldest column, 12-month total

  1. #1
    Registered User
    Join Date
    Dubai, UAE
    MS-Off Ver
    Excel 2007

    Question Inserting new column, hiding oldest column, 12-month total


    Could anyone help me with the following problem:

    I have a sheet with monthly cash flow calculations (inflow, outflow, net cash and cumulative cash) and a 12-month total column. At the end of each month, I want to add a column with a new month and hide the oldest one, in a way that only the most recent 12 months are visible. The 12-month total should also reflect the sum of only the latest 12 months and drop more than 12 month old figures.

    How should I go about it using macros?

    I would appreciate the help.

  2. #2
    Forum Expert Palmetto's Avatar
    Join Date
    South Eastern, USA
    MS-Off Ver
    XP, 2007, 2010

    Re: Inserting new column, hiding oldest column, 12-month total

    Why use VBA at all when you can simply do this using a Pivot Table based on a Table or a dynamic named range as the data source? Since you are using Excel 2007, you could convert the range of cells into a Table. Excel will automatically apply the table formats to the new column.

    With a Pivot Table based on the data table, you simply refresh the PT and filter out the month you no longer need. It is more efficient than VBA.

    Do you know . . . ?

    You can leave feedback and add to the reputation of all who contributed a helpful response to your solution by clicking the star icon located at the left in one of their post in this thread.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1