Vba to copy a formula down to last row

[p4nny]

Hi

I have a formula in cell Q2. Using VBA, I would like to copy this down to the last row of column P

Thanks in advance for your help

 

[SteveO59L]

something along the lines of

Dim lastrow As Long
lastrow = Cells(Rows.Count, "p").End(xlUp).Row

Range("q2").Copy
Range("q3:q" & lastrow).Select
ActiveSheet.Paste

 

[Michael M]

Something like

range(Q2:Q" &  cells(rows.count,"P").end(xlup).row).formula = "=yourformula"

but if you post the formula that is in Q2 it would help

 

[Mike LH]

Hi,

Try this

Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "P").End(xlUp).Row
Range("Q2:Q" & Lastrow).FillDown

 

[steve the fish]

This avoids a possible error should last row in P be in row 2 or less. It also declares the sheet its working on or excel will use the activesheet when the macro is run. Obviously change the sheet name to the one you require:

Sub Filling()

Dim myWS As Worksheet
Dim x As Integer

Set myWS = Sheets("Sheet1")
x = myWS.Range("P" & Rows.Count).End(xlUp).Row

If x > 2 Then myWS.Range("Q2:Q" & x).FillDown

End Sub

 

[ratsalad]

I have a similar issue, looking to do this with all sheets. I have something where I want to select all sheets, insert two columns at the beginning. Type headers into specific cells, each sheet. put formula in specific cells, then fill down - on each sheet. Then I want to paste the range of each sheet onto the first sheet. The problem is that it uses the active.sheet and selects the range from that sheet only for the fill down and I can't get past this part for the copy and paste of each sheet. The range is different on each sheet, so how do I get it to auto detect the range for each sheet and fill down based on the range in that sheet?

Sub tpstatmacro()
'
' tpstatmacro Macro
'
' Keyboard Shortcut: Ctrl+Shift+M
'
    Dim ws As Worksheet
    Application.ScreenUpdating = False
    For Each ws In ActiveWorkbook.Sheets
         'False to extend the current selection to include
         ' any previously selected objects and the specified object
        ws.Select False
    Next ws
    Columns("A:B").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("A4").Select
    ActiveCell.FormulaR1C1 = "SW Eff"
    Range("B4").Select
    ActiveCell.FormulaR1C1 = "FA"
    Range("B5").Select
    ActiveCell.FormulaR1C1 = _
        "=RIGHT(CELL(""filename"",R[-4]C[-1]),LEN(CELL(""filename"",R[-4]C[-1]))-FIND(""]"",CELL(""filename"",R[-4]C[-1])))"
    Range("B5").Select
    Selection.AutoFill Destination:=Range("B5:B43")
    Range("B5:B43").Select
    Range("A1:O43").Select
    Range("B5").Activate
    Selection.Copy
    ActiveSheet.Previous.Select
    Range("A4").Select
    Application.Run "PERSONAL.XLSB!PasteValue"
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveCell.SpecialCells(xlLastCell).Select
    Range("A46").Select
    Application.ScreenUpdating = True
End Sub