none
Excel 2003 Macros in Excel 2010

    Question

  • Hello Everyone - 

    The company I work for recently upgraded to Office 2010. There is one department that heavily uses Excel and Access macros.  Before the upgrade they had a macro that imports data from an Access 2003 database into an Excel 2003 workbook.  This macro running in Office 2003 would run for about 3 hours total time.  After the upgrade and running the same macro in Office 2010 runs for over 12 hours.  Is there anything that can be done to resolve this or any suggestions to increase performance of macros in Excel 2010?

    Thursday, November 17, 2011 6:11 PM

All replies

  • Hi,

    With the release of Office 2007 Microsoft released a new driver, Microsoft.ACE.OLEDB.12.0. But has the department also upgraded from Access 2003 to Access 2010? If so then I advice You to download and use this driver.

    Is it a lot of calculation in Excel during the import process? If so, You may consider to temporarily switch of the automatic calculation mode in Excel.

    See if by saving the Excel workbook in the Excel 2010 file format will make any impact on the performance.

    Edit: Check the active native add-ins, uncheck all, add them one by one back to see if one of them affect the performance.


    Kind regards, Dennis (http://xldennis.wordpress.com/)
    • Edited by XL-Dennis Friday, November 18, 2011 11:58 AM
    Friday, November 18, 2011 11:56 AM
  • Might want to start using PowerPivot for this.
    Friday, November 18, 2011 2:12 PM
  • Is Screenupdating turned off at the beginning of the macro ? If not insert the line below as one of the first statements in the macro:

     

    Application.Screenupdating=False

    Friday, November 18, 2011 2:50 PM
  • Yes I have upgraded them to Access 2010 as well.  I have modified the code to use the Microsoft.ACE.OLEDB.12.0 driver as well but neither of these have provided any better run times.
    Tuesday, November 22, 2011 12:48 PM
  • Application.ScreenUpdating=False : Thank you for the suggestion however this did not produce any better run times.  They are still about 4 times slower than Office 2003.
    Tuesday, November 22, 2011 12:52 PM
  • It's quite remarkable with the negative performance... Obviously it must be something that is triggered when Your solution is being executed...

    Which ADO version did You use in Excel 2003 and which version is now being used in Excel 2010?

    Have You made any changes with the anti-virus software? What would the result be if You temporarily turn it off?

     


    Kind regards, Dennis (http://xldennis.wordpress.com/)
    Wednesday, November 23, 2011 12:03 AM
  • This is what I have been doing to work out what is going on.

    Within your macros output the time at the start and end of each sub to work out where the slowdown is.

    Then once you have found the problem sub or function output the time after each statement, again to work out where the slowdown is.

    When you have worked out what is causing the slowdown, rewrite the line of code and you should find it is as quick if not quicker than before.

    I had an issue where an excel application would take 3 seconds to process a file in 2003, in 2010 it took 1.5 minutes.   Tracking the error in the above way the biggest hog was  a statement that included the following line:

    Workbooks(strconstAName).Sheets(strWSToPaste).Copy before:=Workbooks(strCurrentWB).Sheets(1)

    I rewrote it by adding a sheet to the workbook, reorderring the workbook then copying a range of cells across rather than the entire sheet, this reduced the time to process the file to 2 seconds.

    Hope this helps.

    Other things to check is that you are not runnning in compatibility mode, also try recreating the workbook in a 2010 workbook by copying the objects, forms, modules and saving as an xlsm.  Finally never be afraid to try something that shouldn't work, like altering a for loop to a do loop, as this can also help.

     

    Matt

     

     

     

    • Proposed as answer by Jess_Wundring Thursday, December 15, 2011 9:54 PM
    Tuesday, November 29, 2011 9:36 AM
  • Thank you for your replies.

     

    I have tried turning of Anti-Virus, Changed ADO from JET 4 to ACE 12, and adding time stamps to see if there's a certain spot that's slower than the old version and none of these have provided a solution or a reason as to why its running slower.  

    I tried copying everything to a new xlsm instead of just saving it as one and this didn't help either.

    Thursday, December 01, 2011 3:51 PM
  • Hi,

    I guess we may run out of ideas. But given it take 3 hours to execute it must do something extensive. If possible it would be great if You can provide the forum with the code involved. Do You have any other related procedures that may get triggered.

    A data connection involves three steps, create a connection, do the data operation, release all variables and close down. Is it possible for You to first test the connection and the close down process, next add piece by piece of the data operation. That is, if it possible to split the data operation into smaller code pieces.

    BTW, did You test to remove all present add-ins and then add them back one by one? 


    Kind regards, Dennis (http://xldennis.wordpress.com/)
    Thursday, December 01, 2011 9:49 PM
  • Dennis - Thank you for your quick replies. I have tested the connection by creating a different spreadsheet and looped through all the records within the database within about 1 - 2 seconds so I don't believe that is the issue here.  For the add-ins, there are no add-ins present while running these macros in question.

    I have attached the code in question.  When executing this it gets ran from "ELIBR_Run".  I have also attached a screen shot of the defined names that are also used in this spreadsheet.

    Option Explicit
    
    Sub ver1Q10_CSV_Run()
      ActiveCell.Select
      Sheets("Valuation").Select
      Sheets("Output").Range("A11:T65536").ClearContents
      Range("Interpretation") = "Interp 1": Range("ProdGrp") = "LIBR"
      Call master_carvm_style(Range("ProdGrp") & " & " & Range("Interpretation"))
      Range("Interpretation") = "Interp 1": Range("ProdGrp") = "ELIBR"
      Call master_carvm_style(Range("ProdGrp") & " & " & Range("Interpretation"))
    End Sub
    
    Sub ELIBR_Run()
     Application.EnableCancelKey = xlDisabled
      Application.ScreenUpdating = False
      'ActiveCell.Select
      'Sheets("Valuation").Select
      'Sheets("Output").Range("A11:T65536").ClearContents
      Range("Interpretation") = "Interp 1": Range("ProdGrp") = "ELIBR"
      Call master_carvm_style(Range("ProdGrp") & " & " & Range("Interpretation"))
      'Range("Interpretation") = "Interp 4": Range("ProdGrp") = "ELIBR"
      'Call master_carvm_style(Range("ProdGrp") & " & " & Range("Interpretation"))
      'Range("Interpretation") = "Interp 2": Range("ProdGrp") = "ELIBR"
      'Call master_carvm_style(Range("ProdGrp") & " & " & Range("Interpretation"))
      Application.ScreenUpdating = True
    End Sub
    
    Sub LIBR_Run()
      ActiveCell.Select
      Sheets("Valuation").Select
      Sheets("Output").Range("A11:T65536").ClearContents
      Range("Interpretation") = "Interp 1": Range("ProdGrp") = "LIBR"
      Call master_carvm_style(Range("ProdGrp") & " & " & Range("Interpretation"))
      Range("Interpretation") = "Interp 4": Range("ProdGrp") = "LIBR"
      Call master_carvm_style(Range("ProdGrp") & " & " & Range("Interpretation"))
      Range("Interpretation") = "Interp 2": Range("ProdGrp") = "LIBR"
      Call master_carvm_style(Range("ProdGrp") & " & " & Range("Interpretation"))
    End Sub
    
    Sub Batch_Run()
      ActiveCell.Select
      Sheets("Valuation").Select
      Sheets("Output").Range("A11:Z65536").ClearContents
      Range("Interpretation") = "Interp 1": Range("ProdGrp") = "LIBR"
      Call master_carvm_style(Range("ProdGrp") & " & " & Range("Interpretation"))
    '  Range("Interpretation") = "Interp 4": Range("ProdGrp") = "LIBR"
    '  Call master_carvm_style(Range("ProdGrp") & " & " & Range("Interpretation"))
    '  Range("Interpretation") = "Interp 2": Range("ProdGrp") = "LIBR"
    '  Call master_carvm_style(Range("ProdGrp") & " & " & Range("Interpretation"))
      Range("Interpretation") = "Interp 1": Range("ProdGrp") = "ELIBR"
      Call master_carvm_style(Range("ProdGrp") & " & " & Range("Interpretation"))
    '  Range("Interpretation") = "Interp 4": Range("ProdGrp") = "ELIBR"
    '  Call master_carvm_style(Range("ProdGrp") & " & " & Range("Interpretation"))
    '  Range("Interpretation") = "Interp 2": Range("ProdGrp") = "ELIBR"
    '  Call master_carvm_style(Range("ProdGrp") & " & " & Range("Interpretation"))
    End Sub
    
    Sub master_carvm_style(ProdGrpMethod As String)
      Dim i As Long, D As Long
    Sheets("Valuation").Select
    Sheets("Output").Range("C2") = Now
    Application.DisplayAlerts = False
    '  d = 0
    '  For i = 1 To ActiveWorkbook.Sheets.Count
    '    If Left(Sheets(i - d).Name, 6) = "Output" Then
    '      If Not Sheets(i - d).Name = "Output - Template" Then
    '        Sheets(i - d).Delete
    '        d = d + 1
    '      End If
    '    End If
    '  Next i
    'Application.DisplayAlerts = True
      Sheets("Valuation").Select
      If Not ProdGrpMethod = Range("ProdGrp") & " & " & Range("Interpretation") Then
        Stop
        ProdGrpMethod = Range("ProdGrp") & " & " & Range("Interpretation")
      End If
      Select Case Range("ProdGrp")
      Case "ELIBR"
        Call ImportFromAccessTable_ELIBR(Range("ELIBRmdb"), Range("ELIBRtbl"), ProdGrpMethod)
      Case "LIBR"
        Call ImportFromAccessTable_ELIBR(Range("LIBRmdb"), Range("LIBRtbl"), ProdGrpMethod)
      Case Else
        Stop
      End Select
    Sheets("Output").Range("C3") = Now
    Sheets("Output").Select
    End Sub
    
    Sub ImportFromAccessTable_ELIBR(DBFullName As String, TableName As String, pgm As String)
    ' Example: ADOImportFromAccessTable "C:\FolderName\DataBaseName.mdb", _
        "TableName", Range("C1")
      Dim cn As ADODB.Connection, rs As ADODB.Recordset, intColIndex As Integer
    '    Set TargetRange = TargetRange.Cells(1, 1)
        ' open the database
        Set cn = New ADODB.Connection
        cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
            DBFullName & ";"
        Set rs = New ADODB.Recordset
    Application.Calculation = xlCalculationManual
        With rs
            ' open the recordset
            .Open TableName, cn, adOpenStatic, adLockOptimistic, adCmdTable
            ' all records
            '.Open "SELECT * FROM " & TableName & _
                " WHERE [FieldName] = 'MyCriteria'", cn, , , adCmdText
            ' filter records
            
    '        RS2WS rs, TargetRange ' write data from the recordset to the worksheet
            
    '        ' optional approach for Excel 2000 or later (RS2WS is not necessary)
    '        For intColIndex = 0 To rs.Fields.Count - 1 ' the field names
    '            TargetRange.Offset(0, intColIndex).Value = rs.Fields(intColIndex).Name
    '        Next
    Dim myCnt As Integer, scrn_update As String
    Dim beginT As Date, currT As Date, remainT As Date
    beginT = Now
    Sheets("Valuation").Range("c7").Select
            Do While Not rs.EOF
    If rs.AbsolutePosition < 10 Then
        scrn_update = "Calculating... Policies Remaining: " & Format(rs.RecordCount - rs.AbsolutePosition + 1, "#,##0")
        Application.StatusBar = scrn_update
    Else
        If rs.AbsolutePosition Mod 10 = 0 Then
            currT = Now
            remainT = (currT - beginT) / (rs.AbsolutePosition) * (rs.RecordCount - rs.AbsolutePosition + 1)
            scrn_update = "Calculating... Policies Remaining: " & Format(rs.RecordCount - rs.AbsolutePosition + 1, "#,##0") & "; approx completion: " & Format(remainT, "hh:mm:ss")
            Application.StatusBar = scrn_update
        End If
    End If
    '''          For intColIndex = 0 To rs.Fields.Count - 1 ' the field names
    '''              TargetRange.Offset(rs.AbsolutePosition, intColIndex).Value = rs.Fields(intColIndex).Value
    '''          Next
            'Load Policy
                'Sheets("output").Range("counter") = myCnt
                Sheets("Valuation").Select
                Sheets("Valuation").Range("c9") = rs.Fields(5).Value 'Issue Date
                Sheets("Valuation").Range("c10") = rs.Fields(4).Value 'Issue Age
                If IsNull(rs.Fields(3).Value) Then
                  Sheets("Valuation").Range("c11") = "F" 'Gender
                Else
                  Sheets("Valuation").Range("c11") = rs.Fields(3).Value 'Gender
                End If
                Sheets("Valuation").Range("b6") = rs.Fields(1).Value 'Plancode
                Sheets("Valuation").Range("b7") = rs.Fields(2).Value 'Policy Num
                Sheets("Valuation").Range("c21") = rs.Fields(7).Value / 100 'IAV Rollup Rate
                Sheets("Valuation").Range("c27") = rs.Fields(9).Value  'Annual Pmt
                If rs.Fields(13).Value < rs.Fields(11).Value Then
                  Sheets("Valuation").Range("c13") = rs.Fields(11).Value 'Use Contract Value for IAV Value
                Else
                  Sheets("Valuation").Range("c13") = rs.Fields(13).Value 'IAV Value
                End If
                Sheets("Valuation").Range("c12") = rs.Fields(11).Value 'Contract Value
                Sheets("Valuation").Range("c30") = rs.Fields(14).Value 'Stat Reserve
                Sheets("Valuation").Range("c24") = rs.Fields(16).Value / 100 'Int Fee
            'Process Scenarios
                Call scen_carvm_style("Output", rs, pgm)
              
              rs.MoveNext
            Loop
    '        TargetRange.Offset(1, 0).CopyFromRecordset rs  ' the recordset data
    
        End With
    Application.StatusBar = False
    Application.Calculation = xlCalculationAutomatic
        rs.Close
        Set rs = Nothing
        cn.Close
        Set cn = Nothing
    End Sub
    
    Sub scen_carvm_style(outsheet As String, rs As ADODB.Recordset, pgm As String)
    Dim myCnt1 As Integer
    Dim myCnt2 As Integer
    Dim myRange1 As Range
    
    Dim selectYr As Long, next_policy As Long, rsrvFound As Boolean
    Dim Total_Pmts(1 To 20) As Double
    Dim Total_Fees(1 To 20) As Double
    Dim firstElect As Long, lastElect As Long
    
    Dim maxProd As String, maxPolicy As String
    Dim maxIssD As Date, maxIssA As Long, maxIssG As String
    Dim maxFV As Double, maxIAV As Double
    Dim maxRRate As Double, maxFRate As Double
    Dim maxPmt As Double, maxSTAT As Double, currD As Long, maxD As Long, maxD_Incr As Long
    Dim maxDB_Incr As Double, maxVAL_Incr As Double, maxZERO_Incr As Double
    Dim maxFEE_Incr As Double, maxSEGMENT_Incr As Double
    Dim maxDB As Double, maxVAL As Double, maxZERO As Double, maxFEE As Double
    Dim maxSEGMENT As Double
    
    On Error GoTo CreateNewSheet
    
    
    'For myCnt1 = 0 To 1 '99
      Application.Calculate
      currD = Sheets("Valuation").Range("e8")
      
      maxProd = ""
      maxD = 0
      maxDB = 0
      maxVAL = 0
      maxZERO = 0
      maxFEE = 0
      maxSEGMENT = 0
      maxD_Incr = 0
      maxDB_Incr = 0
      maxVAL_Incr = 0
      maxZERO_Incr = 0
      maxFEE_Incr = 0
      maxSEGMENT_Incr = 0
    
      If Sheets("Valuation").Range("c27") > 0 Then
          Sheets("Valuation").Range("c26") = rs.Fields(4).Value
          Application.Calculate
          
          If Sheets("Valuation").Range("c41") > rs.Fields(14).Value Then
            maxProd = rs.Fields(1).Value
            maxPolicy = rs.Fields(2).Value
            maxIssD = rs.Fields(5).Value
            maxIssA = rs.Fields(4).Value
            If IsNull(rs.Fields(3).Value) Then
              maxIssG = "F"
            Else
              maxIssG = rs.Fields(3).Value
            End If
            maxFV = rs.Fields(11).Value
            maxIAV = rs.Fields(13).Value
            maxRRate = rs.Fields(7).Value
            maxFRate = Sheets("Valuation").Range("c24")
            maxPmt = rs.Fields(9).Value
            maxSTAT = rs.Fields(14).Value
            
            maxD = currD
            maxDB = Sheets("Valuation").Range("x2")
            maxVAL = Sheets("Valuation").Range("ab2")
            maxZERO = Sheets("Valuation").Range("af2")
            maxFEE = Sheets("Valuation").Range("m2")
            maxSEGMENT = Sheets("Valuation").Range("c41")
    'If Range("d41") = "CSV" Then Stop
          End If
          If Sheets("Val - Increasing").Range("c41") > rs.Fields(14).Value Then
            maxProd = rs.Fields(1).Value
            maxPolicy = rs.Fields(2).Value
            maxIssD = rs.Fields(5).Value
            maxIssA = rs.Fields(4).Value
            If IsNull(rs.Fields(3).Value) Then
              maxIssG = "F"
            Else
              maxIssG = rs.Fields(3).Value
            End If
            maxFV = rs.Fields(11).Value
            maxIAV = rs.Fields(13).Value
            maxRRate = rs.Fields(7).Value
            maxFRate = Sheets("Valuation").Range("c24")
            maxPmt = rs.Fields(9).Value
            maxSTAT = rs.Fields(14).Value
            
            maxD_Incr = selectYr + 1
            maxDB_Incr = Sheets("Val - Increasing").Range("x2")
            maxVAL_Incr = Sheets("Val - Increasing").Range("ab2")
            maxZERO_Incr = Sheets("Val - Increasing").Range("af2")
            maxFEE_Incr = Sheets("Val - Increasing").Range("m2")
            maxSEGMENT_Incr = Sheets("Val - Increasing").Range("c41")
          End If
          
      Else
        If 50 - (rs.Fields(4).Value + 1) > 0 Then
          firstElect = 50 - rs.Fields(4).Value
        Else
          firstElect = 1
        End If
        If firstElect < currD Then
          firstElect = currD
        End If
        If UCase(Trim(Range("Yr1CARVM?"))) = "Y" Then
          lastElect = firstElect
        Else
          lastElect = Range("Accum_yrs")
        End If
        
        For selectYr = firstElect To lastElect
          If selectYr > firstElect Then
            Do Until (rs.Fields(4).Value + selectYr) Mod 5 = 0 Or selectYr = lastElect
              selectYr = selectYr + 1
            Loop
          End If
          
          Sheets("Valuation").Range("c26") = rs.Fields(4).Value + selectYr
          Application.Calculate
          
          If Sheets("Valuation").Range("c41") > rs.Fields(14).Value Or Sheets("Val - Increasing").Range("c41") > rs.Fields(14).Value Then
            If Sheets("Valuation").Range("c41") > maxSEGMENT Then
              maxProd = rs.Fields(1).Value
              maxPolicy = rs.Fields(2).Value
              maxIssD = rs.Fields(5).Value
              maxIssA = rs.Fields(4).Value
              
              If IsNull(rs.Fields(3).Value) Then
                maxIssG = "F"
              Else
                maxIssG = rs.Fields(3).Value
              End If
              maxFV = rs.Fields(11).Value
              maxIAV = rs.Fields(13).Value
              maxRRate = rs.Fields(7).Value
              maxFRate = Sheets("Valuation").Range("c24")
              maxPmt = rs.Fields(9).Value
              maxSTAT = rs.Fields(14).Value
              
              maxD = selectYr + 1
              maxDB = Sheets("Valuation").Range("ac2")
              maxVAL = Sheets("Valuation").Range("ag2")
              maxZERO = Sheets("Valuation").Range("ak2")
              maxFEE = Sheets("Valuation").Range("m2")
              maxSEGMENT = Sheets("Valuation").Range("c41")
    'If Range("d41") = "CSV" Then Stop
            End If
             If Sheets("Val - Increasing").Range("c41") > maxSEGMENT_Incr Then
              maxProd = rs.Fields(1).Value
              maxPolicy = rs.Fields(2).Value
              maxIssD = rs.Fields(5).Value
              maxIssA = rs.Fields(4).Value
              If IsNull(rs.Fields(3).Value) Then
                maxIssG = "F"
              Else
                maxIssG = rs.Fields(3).Value
              End If
              maxFV = rs.Fields(11).Value
              maxIAV = rs.Fields(13).Value
              maxRRate = rs.Fields(7).Value
              maxFRate = Sheets("Valuation").Range("c24")
              maxPmt = rs.Fields(9).Value
              maxSTAT = rs.Fields(14).Value
            
              maxD_Incr = selectYr + 1
              maxDB_Incr = Sheets("Val - Increasing").Range("x2")
              maxVAL_Incr = Sheets("Val - Increasing").Range("ab2")
              maxZERO_Incr = Sheets("Val - Increasing").Range("af2")
              maxFEE_Incr = Sheets("Val - Increasing").Range("m2")
              maxSEGMENT_Incr = Sheets("Val - Increasing").Range("c41")
            End If
          End If
        Next selectYr
      End If
    'Next myCnt1
    
    If maxProd <> "" Then
    'Stop
        Sheets(outsheet).Select
        next_policy = Range("c7") + 1
        Range("B10").Offset(next_policy, -1) = pgm
        Range("B10").Offset(next_policy, 0) = maxProd
        Range("B10").Offset(next_policy, 1) = maxPolicy
        Range("B10").Offset(next_policy, 2) = maxIssD
        Range("B10").Offset(next_policy, 3) = maxIssA
        Range("B10").Offset(next_policy, 4) = maxIssG
        Range("B10").Offset(next_policy, 5) = maxFV
        Range("B10").Offset(next_policy, 6) = maxIAV
        Range("B10").Offset(next_policy, 7) = maxRRate / 100
        Range("B10").Offset(next_policy, 8) = maxFRate
        Range("B10").Offset(next_policy, 9) = maxPmt
        Range("B10").Offset(next_policy, 10) = maxSTAT
        Range("B10").Offset(next_policy, 11) = currD
        Range("B10").Offset(next_policy, 12) = maxD
        Range("B10").Offset(next_policy, 13) = maxDB
        Range("B10").Offset(next_policy, 14) = maxVAL
        Range("B10").Offset(next_policy, 15) = maxZERO
        Range("B10").Offset(next_policy, 16) = maxFEE
        Range("B10").Offset(next_policy, 17) = maxSEGMENT
        Range("B10").Offset(next_policy, 18) = maxD_Incr
        Range("B10").Offset(next_policy, 19) = maxDB_Incr
        Range("B10").Offset(next_policy, 20) = maxVAL_Incr
        Range("B10").Offset(next_policy, 21) = maxZERO_Incr
        Range("B10").Offset(next_policy, 22) = maxFEE_Incr
        Range("B10").Offset(next_policy, 23) = maxSEGMENT_Incr
        Range("B10").Offset(next_policy, 24).FormulaR1C1 = "=MAX(RC[-7]-RC[-14],RC[-1]-RC[-14],0)"
        Application.Calculate
    End If
    
    Exit Sub
    CreateNewSheet:
    Stop
    If Err = 9 Then
      Call CopyTemplate(outsheet)
      Resume
    Else
      Stop
      Resume
    End If
    End Sub
    
    Sub CopyTemplate(outsheet As String)
      Sheets.Add After:=Sheets("Output - Template")
      ActiveSheet.Name = outsheet
      Sheets("Output - Template").Cells.Copy
    '    Cells.Select
    '    Selection.Copy
      ActiveSheet.Paste
      Range("A1").Select
    
      Range("A1").Formula = "=COUNTA(G:G)-2"
    End Sub
    Sub CopyCase()
    Dim CPY_Prod As String, CPY_Policy As String, CPY_Idate As Date, CPY_Age As Integer, CPY_RX As Double
    Dim CPY_FV As Double, CPY_IAV As Double, CPY_Rollup As Double, r As Integer, CPY_Sex As String
    Dim CPY_Fee As Double, CPY_Pmt As Double
    r = ActiveCell.Row
    
    CPY_Prod = Cells(r, 2)
    CPY_Policy = Cells(r, 3)
    CPY_Idate = Cells(r, 4)
    CPY_Age = Cells(r, 5)
    CPY_Sex = Cells(r, 6)
    CPY_FV = Cells(r, 7)
    CPY_IAV = Cells(r, 8)
    CPY_Rollup = Cells(r, 9)
    CPY_Fee = Cells(r, 10)
    CPY_Pmt = Cells(r, 11)
    CPY_RX = Cells(r, 12)
    
    Sheets("Valuation").Select
    Cells(6, 2) = CPY_Prod
    Cells(7, 2) = CPY_Policy
    Cells(9, 3) = CPY_Idate
    Cells(10, 3) = CPY_Age
    Cells(11, 3) = CPY_Sex
    Cells(12, 3) = CPY_FV
    Cells(13, 3) = CPY_IAV
    Cells(21, 3) = CPY_Rollup
    Cells(24, 3) = CPY_Fee
    Cells(27, 3) = CPY_Pmt
    Cells(30, 3) = CPY_RX
    
    
    End Sub
    

    Thursday, December 01, 2011 10:02 PM