Macro to Copy formula to end of data

[mcmba]

Hi...strange question...I have a macro that I created that inserts a row, adds a formula. Now I need to copy the formula down to the last row. (You know, like when you double click in the corner of a cell and it copies the formula to the last row with data in the adjoining column).

When I double click while recording my macro it just references the actual column and row number, but it won't aways be the same as the spreadsheet grows daily. So I need a "copy to last row" formula for the macro. But the last row will change everytime I run it.

I hope this makes sense. I am new to this macro stuff!! But trying to learn!

 

[VoG]

To fill down a formula in F2

Sub Myfill()
Dim LR As Long
LR = ActiveSheet.UsedRange.Find("*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
Range("F2").AutoFill Destination:=Range("F2:F" & LR)
End Sub

 

[mcmba]

Awesome .... you are my hero today!

 

[mstod]

Hi - I am trying to do a similar thing, but don't understand Macro code well enough to know how to insert it into the code. I am trying to fill down the following formula in column F, using column A to find last row.

=IF(F2>0, "YES", "").

Do I complete recording the macro, then go into the code and insert it? Where does it go? Obviously, I am a newbie.

 

[Du1896]

To fill down a formula in F2

Sub Myfill()
Dim LR As Long
LR = ActiveSheet.UsedRange.Find("*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
Range("F2").AutoFill Destination:=Range("F2:F" & LR)
End Sub

This is great!

How would I define sheet names? I need these lines to run on two different sheets with different data.

Thanks,
D

 

[Bobinator]

This is an awesome bit of code. It worked perfectly. I am new to editing macros as well, so to the person that asked where to put it. I simply put toward the end. I wanted to copy and paste the values of the formulas. So when I recorded the marco, I copied the entire columns that would have the copied down forumulas and pasted back as values. That was the last step before I stopped recording. I then went into to edit the macro. I had 5 formulas to copy down. You only use one reference to the "DIM LT as Long" line and then string as many of the next 2 lines together as needed, changing the cell reference. Don't put the Sub and End Sub in. Those are the beginning and end of the entire macro. I inserted it at the end just before the code where I copied the columns to copy and paste values. Like this:

  End With
    Dim LR As Long
    LR = ActiveSheet.UsedRange.Find("*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
    Range("K2").AutoFill Destination:=Range("K2:K" & LR)
    LR = ActiveSheet.UsedRange.Find("*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
    Range("L2").AutoFill Destination:=Range("L2:L" & LR)
    LR = ActiveSheet.UsedRange.Find("*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
    Range("M2").AutoFill Destination:=Range("M2:M" & LR)
    LR = ActiveSheet.UsedRange.Find("*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
    Range("N2").AutoFill Destination:=Range("N2:N" & LR)
    LR = ActiveSheet.UsedRange.Find("*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
    Range("O2").AutoFill Destination:=Range("O2:O" & LR)
    Columns("K:O").Select
    Selection.Copy
    Range("K1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("A1").Select
End Sub

End With in the code is the end of the previous step. Columns(K:O).Select is the beginning of the copy and paste values step.

 

[Joe4]

This is an awesome bit of code. It worked perfectly. I am new to editing macros as well, so to the person that asked where to put it. I simply put toward the end. I wanted to copy and paste the values of the formulas. So when I recorded the marco, I copied the entire columns that would have the copied down forumulas and pasted back as values. That was the last step before I stopped recording. I then went into to edit the macro. I had 5 formulas to copy down. You only use one reference to the "DIM LT as Long" line and then string as many of the next 2 lines together as needed, changing the cell reference. Don't put the Sub and End Sub in. Those are the beginning and end of the entire macro. I inserted it at the end just before the code where I copied the columns to copy and paste values. Like this:

  End With
    Dim LR As Long
    LR = ActiveSheet.UsedRange.Find("*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
    Range("K2").AutoFill Destination:=Range("K2:K" & LR)
    LR = ActiveSheet.UsedRange.Find("*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
    Range("L2").AutoFill Destination:=Range("L2:L" & LR)
    LR = ActiveSheet.UsedRange.Find("*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
    Range("M2").AutoFill Destination:=Range("M2:M" & LR)
    LR = ActiveSheet.UsedRange.Find("*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
    Range("N2").AutoFill Destination:=Range("N2:N" & LR)
    LR = ActiveSheet.UsedRange.Find("*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
    Range("O2").AutoFill Destination:=Range("O2:O" & LR)
    Columns("K:O").Select
    Selection.Copy
    Range("K1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("A1").Select
End Sub

End With in the code is the end of the previous step. Columns(K:O).Select is the beginning of the copy and paste values step.

Welcome to the Board!

There is no need to repeat your "LR" calculation. You only need to do it once. You also do not need the "Select" statements. And you can actually do all the AutoFills at once.
So your code could be simplified to:

    Dim LR As Long
    LR = ActiveSheet.UsedRange.Find("*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
    Range("K2:O2").AutoFill Destination:=Range("K2:O" & LR)
    Columns("K:O").Copy
    Range("K1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False