VBA Autofill Two Columns with unique Formula in each Column

Occasional Visitor

We have a report we want to pull, insert two columns, put 2 different formulas in row 2 of each column, then hide the referenced 8 columns.


Then we want to auto fill to the last row with value.


Line 23 has the error. Trying to combine into 1 row by saying =Range("P2:Q" will autofill a shifted formula from column P into Column Q and not fill P at all.



Sub DistributionCheck()
' Macro2 Macro

    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    ActiveCell.FormulaR1C1 = "Canceled"
    ActiveCell.FormulaR1C1 = "Inquiry Only"
    Range("R:Y").EntireColumn.Hidden = True
    Selection.AutoFill Destination:=Range("P2:P" & Range("B" & Rows.Count).End(xlUp).Row)
    Range(Selection, Selection.End(xlDown)).Select

    Selection.AutoFill Destination:=Range("Q2:Q" & Range("B" & Rows.Count).End(xlUp).Row)
    Range(Selection, Selection.End(xlDown)).Select
End Sub




1 Reply
Well, as of this writing, you have 66 views and 0 replies. There's a reason, I suspect: what you describe is not all that clear. What you provide us with is a macro that is (presumably) trying to do what you describe, but NOT the spreadsheet in which all this is to take place. My guess is that nobody can really offer any specific advice without seeing the actual spreadsheet OR, at the very least, an accurate mockup.

Could you provide one or the other?