How do I automate the roll forward my excel working papers?

We can do this in excel with a Macro.

Roll Forward Working Papers: Example

Let’s use an example to illustrate the problem. Every new month we must move our values to the right. We must repeat this for around 20 different sheets.

We will be using a sheet named H4 for our example (below).

A list of some of the repetitive tasks our macro can automate:

  • Copy and Pasting values into the prior month columns
  • Copy and Pasting values into the year end column
  • Clearing values from certain cells each month
  • Clearing values from certain cells/columns at year end
  • Pasting the date onto a sheet for formulas to use the current month

Roll Forward Working Papers: Steps

SAVE YOUR WORKBOOK AS AN .XLSM FILE TYPE!

Step One: Insert Macro

Prerequisites: How to insert a macro

Use the Visual Basic editor to insert the following Macro

Option Explicit
Sub CopyPaste(ByVal I As String)

'Define Variables

    'Monthly Variables
    'this will capture our monthly columns
    Dim CopyMonthly As String
    Dim PasteMonthly As String
    Dim ClearMonthly As String

    'YE Variables
    'this will capture our year end columns
    Dim CopyYE As String
    Dim PasteYE As String
    Dim ClearYE As String

    'Monthly Extra Variables
    'this will capture addittional monthly columns
    Dim CopyExtraMonthly As String
    Dim PasteExtraMonthly As String
    Dim ClearExtraMonthly As String

    'YE Extra Variables
    'this will capture addittional year end columns
    Dim CopyExtraYE As String
    Dim PasteExtraYE As String
    Dim ClearExtraYE As String
    
    'Date Variables
    'this will capture any date cells for our current month
    Dim ReportMonthCopy As String
    Dim ReportMonthPaste As String


'Create Dynamic Strings
'This will allow us to use the same macro for every sheet
    
    'Monthly Variables
    'this will capture our monthly columns
    CopyMonthly = I & "Copy"
    PasteMonthly = I & "Paste"
    ClearMonthly = I & "Clear"
    
    'YE Variables
    'this will capture our year end columns
    CopyYE = I & "CopyYE"
    PasteYE = I & "PasteYE"
    ClearYE = I & "ClearYE"
    
    'Monthly Extra Variables
    'this will capture addittional monthly columns
    CopyExtraMonthly = I & "CopyExtra"
    PasteExtraMonthly = I & "PasteExtra"
    ClearExtraMonthly = I & "ClearExtra"
    
    'YE Extra Variables
    'this will capture addittional year end columns
    CopyExtraYE = I & "CopyExtraYE"
    PasteExtraYE = I & "PasteExtraYE"
    ClearExtraYE = I & "ClearExtraYE"
    
    'Date Variables
    'this will capture any date cells for our current month
    ReportMonthCopy = "ReportingMonth"
    ReportMonthPaste = I & "PasteDate"


'Run Macro
'Only run when check cell is ticked (TRUE)
If Range(ActiveSheet.CheckBoxes(Application.Caller).LinkedCell).Value = True Then
    
    
    'Set Strings as Ranges
    'For the purpose of no clear area or date set
    On Error Resume Next
    
    'Set Monthly Areas
    Dim CopyArea As Range: Set CopyArea = Range(CopyMonthly)
    Dim PasteArea As Range: Set PasteArea = Range(PasteMonthly)
    Dim ClearArea As Range: Set ClearArea = Range(ClearMonthly)
    
    'Set YE Areas
    Dim ClearAreaYE As Range: Set ClearAreaYE = Range(ClearYE)
    Dim CopyAreaYE As Range: Set CopyAreaYE = Range(CopyYE)
    Dim PasteAreaYE As Range: Set PasteAreaYE = Range(PasteYE)

    'Set Monthly Extra Areas
    Dim CopyAreaExtra As Range: Set CopyAreaExtra = Range(CopyExtraMonthly)
    Dim PasteAreaExtra As Range: Set PasteAreaExtra = Range(PasteExtraMonthly)
    Dim ClearAreaExtra As Range: Set ClearAreaExtra = Range(ClearExtraMonthly)
    
    'Set YE Extra Areas
    Dim CopyAreaExtraYE As Range: Set CopyAreaExtraYE = Range(CopyExtraYE)
    Dim PasteAreaExtraYE As Range: Set PasteAreaExtraYE = Range(PasteExtraYE)
    Dim ClearAreaExtraYE As Range: Set ClearAreaExtraYE = Range(ClearExtraYE)
    
    'Set Date Area
    Dim ReportingMonth As Range: Set ReportingMonth = Range(ReportMonthCopy)
    Dim PasteDate As Range: Set PasteDate = Range(ReportMonthPaste)
    
    'Copy and paste YE
    'Must come before Monthly roll forward & only run if YE
    If Range("MonthNumber").Value = 1 Then
        
        'Year End
        CopyAreaYE.Copy
        PasteAreaYE.PasteSpecial xlPasteValues
        Application.CutCopyMode = False
        ClearAreaYE.ClearContents
        
        'Extra Year End
        CopyAreaExtraYE.Copy
        PasteAreaExtraYE.PasteSpecial xlPasteValues
        Application.CutCopyMode = False
        ClearAreaExtraYE.ClearContents
        
    End If
    
    'Monthly
    CopyArea.Copy
    PasteArea.PasteSpecial xlPasteValues
    Application.CutCopyMode = False
    
    'Monthly Extra
    CopyAreaExtra.Copy
    PasteAreaExtra.PasteSpecial xlPasteValues
    Application.CutCopyMode = False
    
    'Date
    ReportingMonth.Copy
    PasteDate.PasteSpecial xlPasteValues
    Application.CutCopyMode = False
    
    'Clear
    ClearArea.ClearContents
    
    'Clear Extra
    ClearAreaExtra.ClearContents

End If

End Sub
Sub RollFwd()

Dim I As String

'Set Sheet as name as I
I = Range(ActiveSheet.CheckBoxes(Application.Caller).LinkedCell).Offset(0, -2).Value

'Run Copy Paste Sub
Call CopyPaste(I)

End Sub

We now have a macro in our workbook which can do all of the tasks we listed above!

Step Two: Set Up

Prerequisites: How to add a named range

We now have the ranges our macro will be working with but how will it:

  • Update our headings to be the right date?
  • Know when it’s the year end?

Set Up Sheet

Create a sheet at the front of the Workbook and name it SetUp

Inset a cell into our workbook being the ReportingMonth

layout

name the range

ReportingMonth

We will also insert a cell being the prior year end (YE0)

layout

name the range

YE0

We will use the these cells to give us the period MonthNumber

formula

=ROUND(DAYS(ReportingMonth,YE0)/30.5,0)

layout

name the range

MonthNumber

Headers

We will use these in our headers so that they will change with the update of one cell

The first &last headers are easy, just reference the ReportingMonth & YE0

=ReportingMonth
=YE0

Use the EOMONTH formula to set the two comparative months

=EOMONTH(ReportingMonth,-1)
=EOMONTH(ReportingMonth,-2)

Step Three: Set up User Interface (UI)

We need to be able to interact with our macro and to relate it to each sheet of our choosing. We will aim for something like this (below).

I’ll leave the cell styling (presentation) up to you but we’ll get a functioning UI up and running!

Roll Forward Sheet

Create a sheet at the front of the Workbook and name it Control

We want to create a row like this. Match the name of our Sheet H4

Add in a check box from the developer tab in the ribbon & place it in our row

Link this check box to the cell to its right. Do this by right clicking the box and linking it via the formula bar.

We can hide our linked column for our User Interface

Right click the box and select Assign Macro.

Select the macro you want to assign to the check box

Our Ranges

For each action we will need to tell our macro where to perform its set tasks. For this we will use named ranges.

Every named range has the same structure

SheetNameFunctionName

Remember, we will be using our sheet named H4 as the example.

Montly Ranges

Copy Range

Set range name as

H4Copy

Range

Paste Range

Set Range name as

H4Paste

Range

Year End Ranges

Copy Range

Set Range name as

H4CopyYE

Range

Paste Range

Set Range name as

H4PasteYE

Roll Forward Working Papers: Result

We now have a sheet which can be rolled forward at the click of a button!

And there you have it, a rolled forward working paper!

Other Available Functions

Monthly & YE Clear

Set an area where the value will be removed from every month or at year end

H4Clear
H4ClearYE

Monthly Date

Set a date cell on the sheet you want to roll forward and name it:

H4ReportingMonth