Using specialcells to apply a formula in a macro to only visible cells

Your code currently seems to be applying repetitive (although different) settings to cell E1. And it would seem also to be putting On Rebate Report into both E1 and E2, surely you only want that to be in cell E1?

There is seldom.. almost never a need to SELECT anything in VBA

Sub Rebate_Exception_v2()
'
' Rebate_Exception_v2 Macro
'

'
Columns("E:E").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
With Range("E1")
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlBottom
    .WrapText = True
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
    .Value = "On Rebate Report"
    With .Font
        .Color = -16776961
        .TintAndShade = 0
    End With
End With
Range("E2").Resize(Range("D65000").End(xlUp).Row - 1).SpecialCells(xlCellTypeVisible).FormulaArray = _
    "=INDEX('Rebate report'!R4C1:R5000C12,MATCH(1,('Rebate report'!C[-4]=RC[-3])*('Rebate report'!C[-3]=RC[-2])*('Rebate report'!C[-2]=RC[-1]),0),1)"
End Sub

LECT anything in VBA to change ranges.