Wiki

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== Microsoft Excel - Macros - Copy - Copy formulae down on a sheet ======


<code excel>
' Copy formulae down on the Test1 sheet.
Sub Copy_Formulae_Down()

    Dim mycell As Variant
    Dim lastrow_Test1 As Long
    Dim Pmt_Curr As Variant
    Dim FX_Rate As Variant



    ' Ask user.
    If ctrl_ask_before_running_subroutine = True Then
        If MsgBox("Copy formulae down?", vbYesNo) = vbNo Then Exit Sub
    End If


    ' Update StatusBar.
    Application.StatusBar = "Copy formulae down..."


    With Workbooks(wb_name)
    
        With .Sheets("Test1")
            
            ' Activate the Test1 sheet.
            .Activate
            
            
            ' Get how many rows of data have been loaded into the sheet.
            lastrow_Test1 = .Cells(Rows.Count, 4).End(xlUp).Row
            ' Prevent line 2 being deleted - as this contains the formulae which need coping down later.
            If lastrow_Test1 < 3 Then
                lastrow_Test1 = 3
            End If
            
    
            ' Ensure that FX_Rate has a default value.
            'For Each mycell In Workbooks(my1042Rec).Sheets("Test1").Range("AE2", Range("AE65536").End(xlUp))
            For Each mycell In .Range("AE2:AE" & lastrow_Test1)
                If Not mycell Like "*[0-9]*" Then mycell.Formula = 1
            Next mycell
            
            
            ' Copy using autofill.
            .Range("A2:A2").AutoFill Destination:=.Range("A2:A" & lastrow_Test1)
            .Range("B2:C2").AutoFill Destination:=.Range("B2:C" & lastrow_Test1)
            .Range("E2").AutoFill Destination:=.Range("E2:E" & lastrow_Test1)
            .Range("K2:L2").AutoFill Destination:=.Range("K2:L" & lastrow_Test1)
            
            
            ' Copy using a resize.  Some say much quicker than alternative solutions, but not proven.
            ' Destination is lastrow -1 as need to exclude header row.
            'Dim rngSource As Range
            'Dim rngTarget As Range
            '    Set rngSource = .Range("A2:A2")
            '    Set rngTarget = Range("A2:A2")
            '    rngTarget.Resize(lastrow_Test1 - 1, rngSource.Columns.Count).Formula = rngSource.Formula
            
            
            ' Ensure that Payment Currency is set to something.  Default to USD for unknowns.
            For Each Pmt_Curr In .Range("G2:G" & lastrow_Test1)
                If RTrim(LTrim(Pmt_Curr)) = "" Then Pmt_Curr.Value = "USD"
            Next Pmt_Curr
            
            
            ' Ensure that the FX Rate is set to a valid number.  Default to 1 for unknowns.
            For Each FX_Rate In .Range("AE2:AE" & lastrow_Test1)
                If IsNumeric(FX_Rate) = False Then FX_Rate.Value = 1
            Next FX_Rate
            
            
            ' Calculations.
            .Range("E2:E" & lastrow_Test1).Calculate
            .Range("A2:A" & lastrow_Test1).Calculate
            .Range("K2:L" & lastrow_Test1).Calculate
            .Range("B2:C" & lastrow_Test1).Calculate
            
            
            ' Now copy and paste formula ranges as values to speed up the file processing.
            .Range("A3:A" & lastrow_Test1) = .Range("A3:A" & lastrow_Test1).Value
            .Range("B3:C" & lastrow_Test1) = .Range("B3:C" & lastrow_Test1).Value
            .Range("E3:E" & lastrow_Test1) = .Range("E3:E" & lastrow_Test1).Value
            .Range("K3:L" & lastrow_Test1) = .Range("K3:L" & lastrow_Test1).Value
            
    
            ' Select A1.
            ScrollTo ActiveSheet.name, "A1"
            
        End With
    End With

    
    ' Clear all objects.
    Set mycell = Nothing
    Set Pmt_Curr = Nothing
    Set FX_Rate = Nothing

End Sub
</code>

or

<code excel>
' Copies formulae down on the Sheet1 sheet.
Sub Copy_Formulae_Down()

    Dim lastrow_Sheet1 As Long


    
    ' Ask user.
    If ctrl_ask_before_running_subroutine = True Then
        If MsgBox("Copy formulae down?", vbYesNo) = vbNo Then Exit Sub
    End If


    ' Update StatusBar.
    Application.StatusBar = "Copy Formulae down..."


    With Workbooks(wb_name)
    
        With .Sheets("sheet1")

            ' Activate the sheet.
            .Activate

            ' Get how many rows of data have been loaded into the sheet.
            lastrow_Sheet1 = .Cells(Rows.Count, 4).End(xlUp).Row
            ' Prevent line 2 being deleted - as this contains the formulae which need coping down later.
            If lastrow_Sheet1 < 3 Then
                lastrow_Sheet1 = 3
            End If

            
            ' Copies formulae down.
            .Range("F2:F2").AutoFill Destination:=.Range("F2:F" & lastrow_Sheet1)
            .Range("F2:F" & lastrow_Sheet1).Calculate
            .Range("F3:F" & lastrow_Sheet1) = .Range("F3:F" & lastrow_Sheet1).Value
    
            .Range("H2:H2").AutoFill Destination:=.Range("H2:H" & lastrow_Sheet1)
            .Range("H2:H" & lastrow_Sheet1).Calculate
            .Range("H3:H" & lastrow_Sheet1) = .Range("H3:H" & lastrow_Sheet1).Value
    
            .Range("J2:J2").AutoFill Destination:=.Range("J2:J" & lastrow_Sheet1)
            .Range("J2:J" & lastrow_Sheet1).Calculate
            .Range("J3:J" & lastrow_Sheet1) = .Range("J3:J" & lastrow_Sheet1).Value
    
            .Range("L2:L2").AutoFill Destination:=.Range("L2:L" & lastrow_Sheet1)
            .Range("L2:L" & lastrow_Sheet1).Calculate
            .Range("L3:L" & lastrow_Sheet1) = .Range("L3:L" & lastrow_Sheet1).Value
    
            .Range("M2:M2").AutoFill Destination:=.Range("M2:M" & lastrow_Sheet1)
            .Range("M2:M" & lastrow_Sheet1).Calculate
            .Range("M3:M" & lastrow_Sheet1) = .Range("M3:M" & lastrow_Sheet1).Value
    
            .Range("T2:T2").AutoFill Destination:=.Range("T2:T" & lastrow_Sheet1)
            .Range("T2:T" & lastrow_Sheet1).Calculate
            .Range("T3:T" & lastrow_Sheet1) = .Range("T3:T" & lastrow_Sheet1).Value
        
            .Range("V2:V2").AutoFill Destination:=.Range("V2:V" & lastrow_Sheet1)
            .Range("V2:V" & lastrow_Sheet1).Calculate
            .Range("V3:V" & lastrow_Sheet1) = .Range("V3:V" & lastrow_Sheet1).Value
    
            .Range("W2:W2").AutoFill Destination:=.Range("W2:W" & lastrow_Sheet1)
            .Range("W2:W" & lastrow_Sheet1).Calculate
            .Range("W3:W" & lastrow_Sheet1) = .Range("W3:W" & lastrow_Sheet1).Value
    
            .Range("X2:X2").AutoFill Destination:=.Range("X2:X" & lastrow_Sheet1)
            .Range("X2:X" & lastrow_Sheet1).Calculate
            .Range("X3:X" & lastrow_Sheet1) = .Range("X3:X" & lastrow_Sheet1).Value
    
            .Range("AB2:AB2").AutoFill Destination:=.Range("AB2:AB" & lastrow_Sheet1)
            .Range("AB2:AB" & lastrow_Sheet1).Calculate
            .Range("AB3:AB" & lastrow_Sheet1) = .Range("AB3:AB" & lastrow_Sheet1).Value
    
            .Range("AC2:AC2").AutoFill Destination:=.Range("AC2:AC" & lastrow_Sheet1)
            .Range("AC2:AC" & lastrow_Sheet1).Calculate
            .Range("AC3:AC" & lastrow_Sheet1) = .Range("AC3:AC" & lastrow_Sheet1).Value
       
            .Range("AE2:AE2").AutoFill Destination:=.Range("AE2:AE" & lastrow_Sheet1)
            .Range("AE2:AE" & lastrow_Sheet1).Calculate
            .Range("AE3:AE" & lastrow_Sheet1) = .Range("AE3:AE" & lastrow_Sheet1).Value
        
            .Range("AG2:AG2").AutoFill Destination:=.Range("AG2:AG" & lastrow_Sheet1)
            .Range("AG2:AG" & lastrow_Sheet1).Calculate
            .Range("AG3:AG" & lastrow_Sheet1) = .Range("AG3:AG" & lastrow_Sheet1).Value
      
            .Range("AH2:AH2").AutoFill Destination:=.Range("AH2:AH" & lastrow_Sheet1)
            .Range("AH2:AH" & lastrow_Sheet1).Calculate
            .Range("AH3:AH" & lastrow_Sheet1) = .Range("AH3:AH" & lastrow_Sheet1).Value
    
            .Range("AJ2:AJ2").AutoFill Destination:=.Range("AJ2:AJ" & lastrow_Sheet1)
            .Range("AJ2:AJ" & lastrow_Sheet1).Calculate
            .Range("AJ3:AJ" & lastrow_Sheet1) = .Range("AJ3:AJ" & lastrow_Sheet1).Value
    
            .Range("AK2:AK2").AutoFill Destination:=.Range("AK2:AK" & lastrow_Sheet1)
            .Range("AK2:AK" & lastrow_Sheet1).Calculate
            .Range("AK3:AK" & lastrow_Sheet1) = .Range("AK3:AK" & lastrow_Sheet1).Value
    
            .Range("AL2:AL2").AutoFill Destination:=.Range("AL2:AL" & lastrow_Sheet1)
            .Range("AL2:AL" & lastrow_Sheet1).Calculate
            .Range("AL3:AL" & lastrow_Sheet1) = .Range("AL3:AL" & lastrow_Sheet1).Value
    
            .Range("AN2:AN2").AutoFill Destination:=.Range("AN2:AN" & lastrow_Sheet1)
            .Range("AN2:AN" & lastrow_Sheet1).Calculate
            .Range("AN3:AN" & lastrow_Sheet1) = .Range("AN3:AN" & lastrow_Sheet1).Value
        
            .Range("AO2:AO2").AutoFill Destination:=.Range("AO2:AO" & lastrow_Sheet1)
            .Range("AO2:AO" & lastrow_Sheet1).Calculate
            .Range("AO3:AO" & lastrow_Sheet1) = .Range("AO3:AO" & lastrow_Sheet1).Value
    
            .Range("AP2:AP2").AutoFill Destination:=.Range("AP2:AP" & lastrow_Sheet1)
            .Range("AP2:AP" & lastrow_Sheet1).Calculate
            .Range("AP3:AP" & lastrow_Sheet1) = .Range("AP3:AP" & lastrow_Sheet1).Value
    
            .Range("AQ2:AQ2").AutoFill Destination:=.Range("AQ2:AQ" & lastrow_Sheet1)
            .Range("AQ2:AQ" & lastrow_Sheet1).Calculate
            .Range("AQ3:AQ" & lastrow_Sheet1) = .Range("AQ3:AQ" & lastrow_Sheet1).Value
    
            .Range("AR2:AR2").AutoFill Destination:=.Range("AR2:AR" & lastrow_Sheet1)
            .Range("AR2:AR" & lastrow_Sheet1).Calculate
            .Range("AR3:AR" & lastrow_Sheet1) = .Range("AR3:AR" & lastrow_Sheet1).Value
    
            .Range("AS2:AS2").AutoFill Destination:=.Range("AS2:AS" & lastrow_Sheet1)
            .Range("AS2:AS" & lastrow_Sheet1).Calculate
            .Range("AS3:AS" & lastrow_Sheet1) = .Range("AS3:AS" & lastrow_Sheet1).Value
    
            .Range("AT2:AT2").AutoFill Destination:=.Range("AT2:AT" & lastrow_Sheet1)
            .Range("AT2:AT" & lastrow_Sheet1).Calculate
            .Range("AT3:AT" & lastrow_Sheet1) = .Range("AT3:AT" & lastrow_Sheet1).Value
      
            .Range("AV2:AV2").AutoFill Destination:=.Range("AV2:AV" & lastrow_Sheet1)
            .Range("AV2:AV" & lastrow_Sheet1).Calculate
            .Range("AV3:AV" & lastrow_Sheet1) = .Range("AV3:AV" & lastrow_Sheet1).Value
    
            .Range("AW2:AW2").AutoFill Destination:=.Range("AW2:AW" & lastrow_Sheet1)
            .Range("AW2:AW" & lastrow_Sheet1).Calculate
            .Range("AW3:AW" & lastrow_Sheet1) = .Range("AW3:AW" & lastrow_Sheet1).Value
    
            .Range("AX2:AX2").AutoFill Destination:=.Range("AX2:AX" & lastrow_Sheet1)
            .Range("AX2:AX" & lastrow_Sheet1).Calculate
            .Range("AX3:AX" & lastrow_Sheet1) = .Range("AX3:AX" & lastrow_Sheet1).Value
    
            .Range("AY2:AY2").AutoFill Destination:=.Range("AY2:AY" & lastrow_Sheet1)
            .Range("AY2:AY" & lastrow_Sheet1).Calculate
            .Range("AY3:AY" & lastrow_Sheet1) = .Range("AY3:AY" & lastrow_Sheet1).Value
        
            .Range("BB2:BB2").AutoFill Destination:=.Range("BB2:BB" & lastrow_Sheet1)
            .Range("BB2:BB" & lastrow_Sheet1).Calculate
            .Range("BB3:BB" & lastrow_Sheet1) = .Range("BB3:BB" & lastrow_Sheet1).Value
    
            .Range("BD2:BD2").AutoFill Destination:=.Range("BD2:BD" & lastrow_Sheet1)
            .Range("BD2:BD" & lastrow_Sheet1).Calculate
            .Range("BD3:BD" & lastrow_Sheet1) = .Range("BD3:BD" & lastrow_Sheet1).Value
    
            .Range("BE2:BE2").AutoFill Destination:=.Range("BE2:BE" & lastrow_Sheet1)
            .Range("BE2:BE" & lastrow_Sheet1).Calculate
            .Range("BE3:BE" & lastrow_Sheet1) = .Range("BE3:BE" & lastrow_Sheet1).Value
    
            .Range("BG2:BG2").AutoFill Destination:=.Range("BG2:BG" & lastrow_Sheet1)
            .Range("BG2:BG" & lastrow_Sheet1).Calculate
            .Range("BG3:BG" & lastrow_Sheet1) = .Range("BG3:BG" & lastrow_Sheet1).Value
    
            .Range("BH2:BH2").AutoFill Destination:=.Range("BH2:BH" & lastrow_Sheet1)
            .Range("BH2:BH" & lastrow_Sheet1).Calculate
            .Range("BH3:BH" & lastrow_Sheet1) = .Range("BH3:BH" & lastrow_Sheet1).Value
    
            .Range("BI2:BI2").AutoFill Destination:=.Range("BI2:BI" & lastrow_Sheet1)
            .Range("BI2:BI" & lastrow_Sheet1).Calculate
            .Range("BI3:BI" & lastrow_Sheet1) = .Range("BI3:BI" & lastrow_Sheet1).Value
    
            .Range("BJ2:BJ2").AutoFill Destination:=.Range("BJ2:BJ" & lastrow_Sheet1)
            .Range("BJ2:BJ" & lastrow_Sheet1).Calculate
            .Range("BJ3:BJ" & lastrow_Sheet1) = .Range("BJ3:BJ" & lastrow_Sheet1).Value
    
            .Range("BK2:BK2").AutoFill Destination:=.Range("BK2:BK" & lastrow_Sheet1)
            .Range("BK2:BK" & lastrow_Sheet1).Calculate
            .Range("BK3:BK" & lastrow_Sheet1) = .Range("BK3:BK" & lastrow_Sheet1).Value
    
            .Range("BL2:BL2").AutoFill Destination:=.Range("BL2:BL" & lastrow_Sheet1)
            .Range("BL2:BL" & lastrow_Sheet1).Calculate
            .Range("BL3:BL" & lastrow_Sheet1) = .Range("BL3:BL" & lastrow_Sheet1).Value
    
            .Range("BM2:BM2").AutoFill Destination:=.Range("BM2:BM" & lastrow_Sheet1)
            .Range("BM2:BM" & lastrow_Sheet1).Calculate
            .Range("BM3:BM" & lastrow_Sheet1) = .Range("BM3:BM" & lastrow_Sheet1).Value
            
        End With
    End With
End Sub
</code>