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:

Rollover_data

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:

Rollover_setup

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:

Rollover_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?

Advertisements

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:

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s




May 2009
M T W T F S S
« Apr   Jun »
 123
45678910
11121314151617
18192021222324
25262728293031

%d bloggers like this: