Fill formulas to last row containing data.

**AnthonyWB** · 06-24-2011, 02:49 PM

Hello everyone. I would like to extend the fomulas in columns AP, AQ, and AT to the last row of data. Currently I paste (via a macro) data from two seperate files into a workbook that acts as a template where the formulas in columns AP, AQ and AT are extended "far down". I would like to stop doing that and simply extend the formulas via code. Any suggestions? Below is my code that I am working with. The formuals are extended in the worksheet called "...Valuation Tool"

Option Explicit
Public Sub CommandButton1_Click()
    Dim CurrentDate As String
    Dim PriorDate As String
    Dim WrkbookPrior As Workbook
    Dim WrkbookCurrent As Workbook
    Dim FilePath1 As String
    Dim FileNamePrefix1 As String
    FileNamePrefix1 = "Option EPMS Valuation "
    FilePath1 = "C:\Users\AHernandez1\Desktop\"
    
With Workbooks.Open(FilePath1 & "Option EPMS Valuation " & Format(TextBoxPriorDate.Text, "m-dd-yy") & ".xlsx")
    .Sheets("Page1-1").Range("A:AO").Copy Workbooks("Option EPMS Valuation Tool.xlsm").Sheets("Prior_Quarter").Range("A1")
End With

With Workbooks.Open(FilePath1 & LCase(FileNamePrefix1) & Format(TextBoxCurrentDate.Text, "m-dd-yy") & ".xlsx")
   .Sheets("Page1-1").Range("A:AO").Copy Workbooks("Option EPMS Valuation Tool.xlsm").Sheets("Current_Quarter").Range("A1")
End With

Workbooks("Option EPMS Valuation Tool.xlsm").Sheets(1).Range("R:R").NumberFormat = "#,##0.00" 'Format to a number
Workbooks("Option EPMS Valuation Tool.xlsm").Sheets(1).Range("AP:AP").NumberFormat = "#,##0.00" 'Format to a number
'WrkbookPrior.Close False
'WrkbookCurrent.Close False

    
End Sub

**AnthonyWB** · 06-24-2011, 03:46 PM

This is somewhat modified from another post. Any other suggestions?

Sub Fill_Formulas_To_Last_Row_With_Data()
'----------------------------------------------------------
Dim Last_Row As Integer
'----------------------------------------------------------
With Workbooks("Option EPMS Valuation Tool.xlsm")
     Last_Row = Range("A" & Rows.Count).End(xlUp).Row
     Range("AP2").AutoFill Destination:=Range("AP2:AP" & LR), Type:=xlFillDefault
     Range("AQ2").AutoFill Destination:=Range("AQ2:AQ" & LR), Type:=xlFillDefault
     Range("AR2").AutoFill Destination:=Range("AR2:AQ" & LR), Type:=xlFillDefault
    End With
End Sub

**AnthonyWB** · 06-24-2011, 04:00 PM

Correction:

Sub Fill_Formulas_To_Last_Row_With_Data()
'----------------------------------------------------------
Dim Last_Row As Integer
'----------------------------------------------------------
With Workbooks("Option EPMS Valuation Tool.xlsm")
     Last_Row = Range("A" & Rows.Count).End(xlUp).Row
     Range("AP2").AutoFill Destination:=Range("AP2:AP" & Last_Row ), Type:=xlFillDefault
     Range("AQ2").AutoFill Destination:=Range("AQ2:AQ" & Last_Row ), Type:=xlFillDefault
     Range("AR2").AutoFill Destination:=Range("AR2:AQ" & Last_Row ), Type:=xlFillDefault
    End With
End Sub