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

Code:
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
 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

iyyi

Active Member
Joined
Jun 5, 2012
Messages
353
Try ...
Code:
Sub Run()
Range("BS5:EB" & Range("BR" & Rows.Count).End(xlUp).Row) = Range("BS4:EB4").Formula
End Sub
 
Last edited:

simonmoran

New Member
Joined
Nov 30, 2011
Messages
28
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 "{"
 

iyyi

Active Member
Joined
Jun 5, 2012
Messages
353
Change the relevant part to:
= Range("BS4:EB4").FormulaArray
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,488
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Try this:
Code:
Sub CopyFormulas()
    Range("BS4:EB4").AutoFill Destination:=Range("BS4:EB" & Cells(Rows.Count, "BR").End(xlUp).Row)

End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,127,861
Messages
5,627,314
Members
416,239
Latest member
Counselor85027

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
Top