Last modified 8 years ago Last modified on 2009-11-13 17:23:20

Convert Yearly Timeseries to monthly data using Excel macros

Simply open up excel, and save your model as an Excel file with macros. Open up a macro and paste the following code.

This code will copy yearly values from your first sheet, to a blank second sheet in a monthly format.

Sub ConvertToMontly()
    startRow = 5
    endCol = 11
    numYears = 100
    
    ' Loop through all the columns
    For col = 1 To endCol
        
        ' Copy the name
        Worksheets(2).Cells(1, col) = Worksheets(1).Cells(1, col)
    
        origRow = 0
        newRow = 1
        Do
            ' Loop for the 12 months
            For m = 1 To 12
                ' Copy the cells
                Worksheets(2).Cells(startRow + (origRow * 12) + m, col) = Worksheets(1).Cells(startRow + origRow, col)
            Next m
            origRow = origRow + 1
            
        Loop Until origRow > numYears + 1
    Next col

End Sub