• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Copy & Paste Until Condition is met (Simple?)

Gregg Wolin

Member
I'd like to create a macro that (on a button click or other event) will copy the formulaic contents of a cell(s) down a column (or across a row) until a condition is met (generally based on the contents of a neighboring cell). The attached workbook illustrates what I am seeking.
 

Attachments

  • vba_copyFormulas.xlsx
    49 KB · Views: 5
Solved copying down part with code below.

Code:
Sub PasteRange()
    Dim Last_Row As Long
    Last_Row = Range("N" & Rows.Count).End(xlUp).Row
    Range("p6:q6").Copy Range("p6:q" & Last_Row)
End Sub

I've been unable to modify for copying ACROSS - Anyone?
 
Last edited:
Not a vba macro but a Power Query offering in the attached. No formulae.
Right-click the table at cell V5 and choose Refresh (this refresh could be done via a one-line macro).
It uses your blue values in column C and nothing else (I've made named ranges of them).
 

Attachments

  • Chandoo48219vba_copyFormulas.xlsx
    58.9 KB · Views: 2
Also, if all the formulae spill you don't need to do any dragging:
Change H6 formula to:
=IF(($G6#>=$C$8)*($G6#<=$C$11),$C$9,0)
and I6 to:
=IF(($G6#>=$C$5)*($G6#<=C$6),$C$13,0)
 
Back
Top