VBA To Copy Formulas Down

simonmoran

New Member
Joined
Nov 30, 2011
Messages
28
So I have a varying amount of data in column BR, the amount of rows will change depending on how much data is being pasted in by the user. I have formulas in columns BS4 through to EB4 that search the data in BR for specific words or phrases

I am trying to write some code that will copy the formula in BS4:EB4 all the way down to the last row in column BR. I then want to paste the data in the same range as values so they are not live.

Using the macro recorder i get the following

PHP:
Sub run()
'
' run Macro
'

'
    Range("BS4").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.AutoFill Destination:=Range("BS4:EB29")
    Range("BS4:EB29").Select
    Range("BS5").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("BS5").Select
    Application.CutCopyMode = False
End Sub

It works perfectly apart from it only runs to the set range! How do I change this so that it runs till the last row in column BR?

Thanks
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Try ...
Code:
Sub Run()
Range("BS5:EB" & Range("BR" & Rows.Count).End(xlUp).Row) = Range("BS4:EB4").Formula
End Sub
 
Last edited:
Upvote 0
Try ...
Code:
Sub Run()
Range("BS5:EB" & Range("BR" & Rows.Count).End(xlUp).Row) = Range("BS4:EB4").Formula
End Sub

That copies all the formulas down but doesnt seem to execute the forumla. I should mention the formulas in BS4:EB4 are array formulas. When the above code executes, the forumlas are copied down but down not have the curly brackets around them "{"
 
Upvote 0
Try this:
Code:
Sub CopyFormulas()
    Range("BS4:EB4").AutoFill Destination:=Range("BS4:EB" & Cells(Rows.Count, "BR").End(xlUp).Row)

End Sub
 
Upvote 0

Forum statistics

Threads
1,213,483
Messages
6,113,919
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top