Macro to create individual reports off of a Master Workbook

I need a way to take my compiled data and divvy it out to new, smaller workbooks for individual agents.
Any ideas?

Here is what I have so far:

Formula:

Sub PasteData()

Dim Wb As Workbook

Dim Ws As Worksheet

Dim Wd As Worksheet         'Ws the source sheet, Wd the object sheet

Dim S As String: S = "Master Workbook"

Dim P As String: P = ActiveWorkbook.Path & "\"

Dim U As String: U = Dir(P)

Dim r As Long: r = 2

For Each Wb In Workbooks

If Wb.Name Like S & "*.xlsx*" Then GoTo SetWd

Next:

    Workbooks.Open Filename:=P & S

SetWd: Set Wd = Workbooks(S).Sheets("Sheet1")

SetaBook:

If U Like S & ".xlsx" Then GoTo GetaBook

Workbooks.Open Filename:=P & U

ActiveSheet.Name = "sheet1"

    Range("C2").Select

    ActiveCell.FormulaR1C1 = _

        "=VLOOKUP(RC[-1],'[Master Workbook.xlsx]All Teams'!C2:C31,2,FALSE)"

    Range("C2").Select

    Selection.AutoFill Destination:=Range("C2:D2"), Type:=xlFillDefault

    Range("C2:D2").Select

    Range("D2").Select

    ActiveCell.FormulaR1C1 = _

        "=VLOOKUP(RC[-1],'[Master Workbook.xlsx]All Teams'!C2:C31,3,FALSE)"

    Range("C2:D2").Select

    Selection.AutoFill Destination:=Range("C2:E2"), Type:=xlFillDefault

    Range("C2:E2").Select

    Range("E2").Select

    ActiveCell.FormulaR1C1 = _

        "=VLOOKUP(RC[-1],'[Master Workbook.xlsx]All Teams'!C2:C31,4,FALSE)"

    Range("C2").Select

    ActiveCell.FormulaR1C1 = _

        "=VLOOKUP(R2C2,'[Master Workbook.xlsx]All Teams'!C2:C31,2,FALSE)"

    Range("C2").Select

    Selection.AutoFill Destination:=Range("C2:AI2"), Type:=xlFillDefault

    Range("C2:AI2").Select

    Range("D2").Select

    ActiveCell.FormulaR1C1 = _

        "=VLOOKUP(R2C2,'[Master Workbook.xlsx]All Teams'!C2:C31,3,FALSE)"

    Range("E2").Select

    ActiveCell.FormulaR1C1 = _

        "=VLOOKUP(R2C2,'[Master Workbook.xlsx]All Teams'!C2:C31,4,FALSE)"

    Range("F2").Select

    ActiveCell.FormulaR1C1 = _

        "=VLOOKUP(R2C2,'[Master Workbook.xlsx]All Teams'!C2:C31,5,FALSE)"

    Range("G2").Select

    ActiveCell.FormulaR1C1 = _

        "=VLOOKUP(R2C2,'[Master Workbook.xlsx]All Teams'!C2:C31,6,FALSE)"

    Range("H2").Select

    ActiveCell.FormulaR1C1 = _

        "=VLOOKUP(R2C2,'[Master Workbook.xlsx]All Teams'!C2:C31,7,FALSE)"

    Range("I2").Select

    ActiveCell.FormulaR1C1 = _

        "=VLOOKUP(R2C2,'[Master Workbook.xlsx]All Teams'!C2:C31,8,FALSE)"

    Range("J2").Select

    ActiveCell.FormulaR1C1 = _

        "=VLOOKUP(R2C2,'[Master Workbook.xlsx]All Teams'!C2:C31,9,FALSE)"

    Range("K2").Select

    ActiveCell.FormulaR1C1 = _

        "=VLOOKUP(R2C2,'[Master Workbook.xlsx]All Teams'!C2:C31,10,FALSE)"

    Range("L2").Select

    ActiveCell.FormulaR1C1 = _

        "=VLOOKUP(R2C2,'[Master Workbook.xlsx]All Teams'!C2:C31,11,FALSE)"

    Range("M2").Select

    ActiveCell.FormulaR1C1 = _

        "=VLOOKUP(R2C2,'[Master Workbook.xlsx]All Teams'!C2:C31,12,FALSE)"

    Range("N2").Select

    ActiveCell.FormulaR1C1 = _

        "=VLOOKUP(R2C2,'[Master Workbook.xlsx]All Teams'!C2:C31,13,FALSE)"

    Range("O2").Select

    ActiveCell.FormulaR1C1 = _

        "=VLOOKUP(R2C2,'[Master Workbook.xlsx]All Teams'!C2:C31,14,FALSE)"

    Range("P2").Select

    ActiveCell.FormulaR1C1 = _

        "=VLOOKUP(R2C2,'[Master Workbook.xlsx]All Teams'!C2:C31,15,FALSE)"

    Range("Q2").Select

    ActiveCell.FormulaR1C1 = _

        "=VLOOKUP(R2C2,'[Master Workbook.xlsx]All Teams'!C2:C31,16,FALSE)"

    Range("R2").Select

    ActiveCell.FormulaR1C1 = _

        "=VLOOKUP(R2C2,'[Master Workbook.xlsx]All Teams'!C2:C31,17,FALSE)"

    Range("S2").Select

    ActiveCell.FormulaR1C1 = _

        "=VLOOKUP(R2C2,'[Master Workbook.xlsx]All Teams'!C2:C31,18,FALSE)"

    Range("T2").Select

    ActiveCell.FormulaR1C1 = _

        "=VLOOKUP(R2C2,'[Master Workbook.xlsx]All Teams'!C2:C31,19,FALSE)"

    Range("U2").Select

    ActiveCell.FormulaR1C1 = _

        "=VLOOKUP(R2C2,'[Master Workbook.xlsx]All Teams'!C2:C31,20,FALSE)"

    Range("V2").Select

    ActiveCell.FormulaR1C1 = _

        "=VLOOKUP(R2C2,'[Master Workbook.xlsx]All Teams'!C2:C31,21,FALSE)"

    Range("W2").Select

    ActiveCell.FormulaR1C1 = _

        "=VLOOKUP(R2C2,'[Master Workbook.xlsx]All Teams'!C2:C31,22,FALSE)"

    Range("X2").Select

    ActiveCell.FormulaR1C1 = _

        "=VLOOKUP(R2C2,'[Master Workbook.xlsx]All Teams'!C2:C31,23,FALSE)"

    Range("Y2").Select

    ActiveCell.FormulaR1C1 = _

        "=VLOOKUP(R2C2,'[Master Workbook.xlsx]All Teams'!C2:C31,24,FALSE)"

    Range("Z2").Select

    ActiveCell.FormulaR1C1 = _

        "=VLOOKUP(R2C2,'[Master Workbook.xlsx]All Teams'!C2:C31,25,FALSE)"

    Range("AA2").Select

    ActiveCell.FormulaR1C1 = _

        "=VLOOKUP(R2C2,'[Master Workbook.xlsx]All Teams'!C2:C31,26,FALSE)"

    Range("AB2").Select

    ActiveCell.FormulaR1C1 = _

        "=VLOOKUP(R2C2,'[Master Workbook.xlsx]All Teams'!C2:C31,27,FALSE)"

    Range("AC2").Select

    ActiveCell.FormulaR1C1 = _

        "=VLOOKUP(R2C2,'[Master Workbook.xlsx]All Teams'!C2:C31,28,FALSE)"

    Range("Z2").Select

    Selection.End(xlToLeft).Select

    Range("B1").Select

    ActiveCell.FormulaR1C1 = "='[Master Workbook.xlsx]All Teams'!R3C2"

    Range("B1").Select

    ActiveCell.FormulaR1C1 = "='[Master Workbook.xlsx]All Teams'!R[2]C"

    Range("B1").Select

    Selection.AutoFill Destination:=Range("B1:AI1"), Type:=xlFillDefault

    Range("B1:AI1").Select

    Range("AF1:AI2").Select

    Selection.ClearContents

    Range("AD2").Select

    ActiveCell.FormulaR1C1 = _

        "=VLOOKUP(R2C2,'[Master Workbook.xlsx]All Teams'!C2:C31,29,FALSE)"

    Range("AE2").Select

    ActiveCell.FormulaR1C1 = _

        "=VLOOKUP(R2C2,'[Master Workbook.xlsx]All Teams'!C2:C31,30,FALSE)"

    Range("AF1").Select

    Cells.Select

    ActiveWindow.Zoom = 85

    ActiveWindow.Zoom = 70

    Cells.EntireColumn.AutoFit

        Cells.Select

    Selection.Copy

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

        :=False, Transpose:=False

    Columns("F:G").Select

    Application.CutCopyMode = False

    Selection.Delete Shift:=xlToLeft

    Columns("H:H").Select

    Selection.Delete Shift:=xlToLeft

    Columns("I:I").Select

    Selection.Delete Shift:=xlToLeft

    Columns("L:L").Select

    Selection.Delete Shift:=xlToLeft

    Selection.Delete Shift:=xlToLeft

    Selection.Delete Shift:=xlToLeft

    Columns("O:P").Select

    Range("P1").Activate

    Selection.Delete Shift:=xlToLeft

    Range("P3").Select

    ActiveCell.FormulaR1C1 = "=IF(R[-1]C=0,""NA"",R[-1]C)"

    Range("P3").Select

    Selection.Copy

    Range("P2").Select

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

        :=False, Transpose:=False

    Range("P3").Select

    Application.CutCopyMode = False

    Selection.ClearContents

    Columns("L:L").Select

    Selection.Delete Shift:=xlToLeft

   

Set Wb = ActiveWorkbook: Set Ws = Wb.Sheets("sheet1") 'Set the source sheet

Range("B3:AE3").Select

    Selection.Copy

    Ws.Range("A1:v12").Copy Wd.Cells(r, 1)    'Copy source range to object sheet

Wb.Close SaveChanges:=False                 'Close this source book

GetaBook: U = Dir()                         'Get the next book

If U = "" Then Exit Sub                     'if the directory is exhausted - quit

GoTo SetaBook: End Sub                      'Process this new book

I tried reverse-engineering a macro that took individual workbooks and compiled them into a master workbook. I don't think I have covered all the holes though. Should I even do it this way or just start from a totally different direction?
Sorry, there is a lot of fluff in there that is just me recording actions.