Roll-over Periodic Workbook – Part 1

It’s quite common in financial reporting to carry over data from one periodic report to its successor – from one month to the next, say. The ‘Current’ data in one report becomes the ‘Previous’ data in the next one; percentage or absolute change might be calculated. Something like this:


The Previous data is brought forward from the predecessor report, and is not to be changed (grey fill); the Current data is entered in this report (green fill).

I thought it would be useful to have a template that (a) helped to set up the data ranges to roll-over, and (b) performed the roll-over, creating a new workbook and copying the data. There’s a worksheet that acts as a control panel:


The parameters are:

  • base name for the workbooks (created as <basename>_yymmdd.xls)
  • date
  • period (as used by the DateAdd function)
  • count (in the sequence of reports).

(“rob” stands for “roll-over book”).

The range set up is done via a form:


This allows you to select pairs of ranges, with a given name, defining names of the form Prev_X and Curr_X. The ranges are checked for non-intersection, single area and same row/column dimensions. You can have as many pairs of ranges as necessary. All other cells are as defined in the template (e.g. with formulas).

Clicking the ‘Roll Over’ button generates a new report from the template, incrementing Date and Count, and copying Curr ranges to Prev ranges. You then edit and save as normal.

I’ll talk about the design tomorrow. There are a couple of Class modules in there – any ideas?


3 Responses to “Roll-over Periodic Workbook – Part 1”

  1. 1 search hotel September 10, 2014 at 12:27 pm

    Spot on with this write-up, I seriously feel this website needs far more attention. I’ll probably be returning to
    read more, thanks for the info!

  1. 1 Roll-over Periodic Workbook – Part 2 « Roy MacLean’s VBA Blog Trackback on May 21, 2009 at 5:40 pm
  2. 2 Smart Inter-Workbook Links « Roy MacLean’s VBA Blog Trackback on June 17, 2009 at 2:28 pm

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

May 2009
« Apr   Jun »

%d bloggers like this: