Macro Speed

Hi All

This is my first macro code, unfortunately it's taking long time to genereate nearely 8 min.

Need help on this

Sub report()

Dim F1 As String
Dim F2 As String
Dim F3 As String

'Formulas for vlookup

F1 = "=+VLOOKUP(RC[-1], Report Apr 20'!C[-1]:C7,6,0)"
F2 = "=+VLOOKUP(RC[-2],' Report Apr 20'!C[-2]:C7,5,0)"
F3 = "=+VLOOKUP(RC[-3],' Report Apr 20'!C[-3]:C6,4,0)"

    Columns("C:E").Select
    Selection.Insert Shift:=xlToRight
[C1].Value = "PC / MAG"
[D1].Value = "BU / LOB"
[E1].Value = "BS / BG"

Application.ScreenUpdating = False


Range("c2").Select
Do While IsEmpty(ActiveCell.Offset(0, -1)) = False
    ActiveCell.FormulaR1C1 = F1
    ActiveCell.Offset(1, 0).Select
    Loop
Range("d2").Select
Do While IsEmpty(ActiveCell.Offset(0, -1)) = False
    ActiveCell.FormulaR1C1 = F2
    ActiveCell.Offset(1, 0).Select
    Loop
Range("E2").Select
  Do While IsEmpty(ActiveCell.Offset(0, -1)) = False
    ActiveCell.FormulaR1C1 = F3
    ActiveCell.Offset(1, 0).Select
    Loop

See if this is any quicker:

Sub reportTMS()

'Formulas for vlookup
Dim F1 As String: F1 = "=VLOOKUP(RC[-1],'Report Apr 20'!C[-1]:C7,6,0)"
Dim F2 As String: F2 = "=VLOOKUP(RC[-2],'Report Apr 20'!C[-2]:C7,5,0)"
Dim F3 As String: F3 = "=VLOOKUP(RC[-3],'Report Apr 20'!C[-3]:C7,4,0)"

Application.ScreenUpdating = False

Columns("C:E").Insert Shift:=xlToRight
[C1:E1].Value = Split("PC / MAG|BU / LOB|BS / BG", "|")

With Range("B2:B" & Range("B" & Rows.Count).End(xlUp).Row)
    .Offset(0, 1).FormulaR1C1 = F1
    .Offset(0, 2).FormulaR1C1 = F2
    .Offset(0, 3).FormulaR1C1 = F3
End With

Application.ScreenUpdating = True
End Sub

Note: if there are a lot of formulae, it might also help to switch calculation to manual and then back to automatic.

Regards

A PvotTable might be a better option