Edit page ← Previous ChangeWiki History

Changes between Initial Version and Version 1 of ConvertYearlyTimeseriesToMonthlyInExcel


Ignore:
Timestamp:
2009-11-13 17:20:17 (10 years ago)
Author:
shermanl
Comment:

--

Legend:

Unmodified
Added
Removed
Modified
  • ConvertYearlyTimeseriesToMonthlyInExcel

    v1 v1  
     1= Convert Yearly Timeseries to monthly data using Excel macros =  
     2 
     3Simply open up excel, and save your  model as an Excel file with macros.  Open up a macro and paste the following code.   
     4 
     5This code will copy yearly values from your first sheet, to a blank second sheet in a monthly format. 
     6 
     7{{{ 
     8Sub ConvertToMontly() 
     9    startRow = 5 
     10    endCol = 11 
     11    numYears = 100 
     12     
     13    ' Loop through all the columns 
     14    For col = 1 To endCol 
     15         
     16        ' Copy the name 
     17        Worksheets(2).Cells(1, col) = Worksheets(1).Cells(1, col) 
     18     
     19        origRow = 0 
     20        newRow = 1 
     21        Do 
     22            ' Loop for the 12 months 
     23            For m = 1 To 12 
     24                ' Copy the cells 
     25                Worksheets(2).Cells(startRow + (origRow * 12) + m, col) = Worksheets(1).Cells(startRow + origRow, col) 
     26            Next m 
     27            origRow = origRow + 1 
     28             
     29        Loop Until origRow > numYears + 1 
     30    Next col 
     31 
     32End Sub 
     33 
     34}}}