Do you understand what the code trying to do?

Sub Test()

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Dim i As Integer
i = 6

Dim SpecifiedDate As String
SpecifiedDate = Cells(4, ActiveCell.Column)
SpecifiedDate = Format(SpecifiedDate, "mm/dd/yyyy")
SpecifiedDate = Replace(SpecifiedDate, "/", "-")

Dim TWB As Workbook
Set TWB = ThisWorkbook

Col = ActiveCell.Column

'CFSPPSUM Data Transfer

Application.Workbooks.Open ("C:\Users\Chris Treeman\My Documents\Dept MathCS Financials\Accounting Files\Dept. Fund Tracking\Daily Compass Report Files\" & "CFSPPSUM - " & SpecifiedDate & ".xls")

While TWB.Worksheets("Import-Data").Cells(i, 3).Value <> "END"
On Error Resume Next
    TWB.Worksheets("Import-Data").Cells(i, Col) = Application.WorksheetFunction.VLookup(TWB.Worksheets("Import-Data").Cells(i, 3).Value, _
Workbooks("CFSPPSUM - " & SpecifiedDate & ".xls").Worksheets("Output Data Sheet").Range("$E$9:$AA$100"), 23, False)

i = i + 1
Wend
Cells(6, ActiveCell.Column).Select

Windows("CFSPPSUM - " & SpecifiedDate & ".xls").Close (False)

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

End Sub
Sub Test_Final_Build()

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

If ActiveCell.Row = 6 Then

Dim SpecifiedDate As String
SpecifiedDate = Cells(4, ActiveCell.Column)
SpecifiedDate = Format(SpecifiedDate, "mm/dd/yyyy")
SpecifiedDate = Replace(SpecifiedDate, "/", "-")

Dim TWB As Workbook
Set TWB = ThisWorkbook

Col = ActiveCell.Column

Dim NSPLoc As String, NSPL As Integer
NSPLoc = Application.WorksheetFunction.Match("NSP SUM", Range("B1:B200"), 0)
NSPL = NSPLoc
Dim D831020Loc As String, D831020L As Integer
D831020Loc = Application.WorksheetFunction.Match("Dept. 831020", Range("B1:B200"), 0)
D831020L = D831020Loc
Dim SPPISUMLoc As String, SPPISUML As Integer, NSPISUMLoc As String, NSPISUML As Integer
SPPISUMLoc = Application.WorksheetFunction.Match("SPP I-SUM", Range("C1:C200"), 0)
SPPISUML = SPPISUMLoc
NSPISUMLoc = Application.WorksheetFunction.Match("NSP I-SUM", Range("C1:C200"), 0)
NSPISUML = NSPISUMLoc

Dim i As Integer, j As Integer, k As Integer, l As Integer, m As Integer, n As Integer, o As Integer, p As Integer, q As Integer, r As Integer, s As Integer, t As Integer, u As Integer
i = 6   'SPP Sum Location
j = 6   'NSP Sum Location
k = 6
l = 6
m = 6
n = 6
o = 6
p = 6
q = 6
r = 6
s = 6
t = 6
u = 6


    'CFNSPSUM Data Transfer
Application.Workbooks.Open ("C:\Users\Chris Treeman\My Documents\Dept MathCS Financials\Accounting Files\Dept. Fund Tracking\Daily Compass Report Files\" & "CFNSPSUM - " & SpecifiedDate & ".xls")
While TWB.Worksheets("Import-Data").Cells(j, 3).Value <> "END"
On Error Resume Next
    TWB.Worksheets("Import-Data").Cells(j, Col) = Application.WorksheetFunction.VLookup(TWB.Worksheets("Import-Data").Cells(j, 3).Value, _
    Workbooks("CFNSPSUM - " & SpecifiedDate & ".xls").Worksheets("Sheet1").Range("$E$11:$BI$150"), 57, False)
    j = j + 1
Wend
    Windows("CFNSPSUM - " & SpecifiedDate & ".xls").Activate     'Sum Total Check Figure Import
        Sheets("Sheet1").Select
            Range("BI106").Copy
    TWB.Sheets("Import-Data").Activate
        Cells(NSPISUML, ActiveCell.Column).PasteSpecial xlPasteValues
Windows("CFNSPSUM - " & SpecifiedDate & ".xls").Close (False)


    'CFSPPSUM Data Transfer
Application.Workbooks.Open ("C:\Users\Chris Treeman\My Documents\Dept MathCS Financials\Accounting Files\Dept. Fund Tracking\Daily Compass Report Files\" & "CFSPPSUM - " & SpecifiedDate & ".xls")
While TWB.Worksheets("Import-Data").Cells(i, 3).Value <> "END"
On Error Resume Next
    TWB.Worksheets("Import-Data").Cells(i, Col) = Application.WorksheetFunction.VLookup(TWB.Worksheets("Import-Data").Cells(i, 3).Value, _
    Workbooks("CFSPPSUM - " & SpecifiedDate & ".xls").Worksheets("Output Data Sheet").Range("$E$9:$AA$100"), 23, False)
    i = i + 1
Wend
Windows("CFSPPSUM - " & SpecifiedDate & ".xls").Activate        'Sum Total Check figure import
    Sheets("Output Data Sheet").Select
        Range("AA80").Copy
    TWB.Sheets("Import-Data").Activate
        Cells(SPPISUML, ActiveCell.Column).PasteSpecial xlPasteValues
Windows("CFSPPSUM - " & SpecifiedDate & ".xls").Close (False)


    'Dept. 831020 Data Transfer
Application.Workbooks.Open ("C:\Users\Chris Treeman\My Documents\Dept MathCS Financials\Accounting Files\Dept. Fund Tracking\Daily Compass Report Files\" & "Dept. 831020 - " & SpecifiedDate & ".xls")
While TWB.Worksheets("Import-Data").Cells(k, 3).Value <> "END"
On Error Resume Next
    TWB.Worksheets("Import-Data").Cells(k, Col) = Application.WorksheetFunction.VLookup(TWB.Worksheets("Import-Data").Cells(k, 3).Value, _
    Workbooks("Dept. 831020 - " & SpecifiedDate & ".xls").Worksheets("Sheet1").Range("$E$10:$AY$100"), 47, False)
    k = k + 1
Wend
Windows("Dept. 831020 - " & SpecifiedDate & ".xls").Close (False)


    'Dept. 831021 Data Transfer
Application.Workbooks.Open ("C:\Users\Chris Treeman\My Documents\Dept MathCS Financials\Accounting Files\Dept. Fund Tracking\Daily Compass Report Files\" & "Dept. 831021 - " & SpecifiedDate & ".xls")
While TWB.Worksheets("Import-Data").Cells(l, 3).Value <> "END"
On Error Resume Next
    TWB.Worksheets("Import-Data").Cells(l, Col) = Application.WorksheetFunction.VLookup(TWB.Worksheets("Import-Data").Cells(l, 3).Value, _
    Workbooks("Dept. 831021 - " & SpecifiedDate & ".xls").Worksheets("Sheet1").Range("$E$10:$AY$100"), 47, False)
    l = l + 1
Wend
Windows("Dept. 831021 - " & SpecifiedDate & ".xls").Close (False)


    'Dept. 831022 Data Transfer
Application.Workbooks.Open ("C:\Users\Chris Treeman\My Documents\Dept MathCS Financials\Accounting Files\Dept. Fund Tracking\Daily Compass Report Files\" & "Dept. 831022 - " & SpecifiedDate & ".xls")
While TWB.Worksheets("Import-Data").Cells(m, 3).Value <> "END"
On Error Resume Next
    TWB.Worksheets("Import-Data").Cells(m, Col) = Application.WorksheetFunction.VLookup(TWB.Worksheets("Import-Data").Cells(m, 3).Value, _
    Workbooks("Dept. 831022 - " & SpecifiedDate & ".xls").Worksheets("Sheet1").Range("$E$10:$AY$100"), 47, False)
    m = m + 1
Wend
Windows("Dept. 831022 - " & SpecifiedDate & ".xls").Close (False)


    'Dept. 831023 Data Transfer
Application.Workbooks.Open ("C:\Users\Chris Treeman\My Documents\Dept MathCS Financials\Accounting Files\Dept. Fund Tracking\Daily Compass Report Files\" & "Dept. 831023 - " & SpecifiedDate & ".xls")
While TWB.Worksheets("Import-Data").Cells(n, 3).Value <> "END"
On Error Resume Next
    TWB.Worksheets("Import-Data").Cells(n, Col) = Application.WorksheetFunction.VLookup(TWB.Worksheets("Import-Data").Cells(n, 3).Value, _
    Workbooks("Dept. 831023 - " & SpecifiedDate & ".xls").Worksheets("Sheet1").Range("$E$10:$AY$100"), 47, False)
    n = n + 1
Wend
Windows("Dept. 831023 - " & SpecifiedDate & ".xls").Close (False)


    'CFNSPS01 Data Transfer
Application.Workbooks.Open ("C:\Users\Chris Treeman\My Documents\Dept MathCS Financials\Accounting Files\Dept. Fund Tracking\Daily Compass Report Files\" & "CFNSPS01 - PROVOST BAYH-DOLE RESTRICTED S" & SpecifiedDate & ".xls")
While TWB.Worksheets("Import-Data").Cells(o, 3).Value <> "END"
On Error Resume Next
    TWB.Worksheets("Import-Data").Cells(o, Col) = Application.WorksheetFunction.VLookup(TWB.Worksheets("Import-Data").Cells(o, 3).Value, _
    Workbooks("CFNSPS01 - PROVOST BAYH-DOLE RESTRICTED S" & SpecifiedDate & ".xls").Worksheets("Sheet1").Range("$E$11:$BI$200"), 57, False)
    o = o + 1
Wend
Windows("CFNSPS01 - PROVOST BAYH-DOLE RESTRICTED S" & SpecifiedDate & ".xls").Close (False)


    'CFNSPS02 Data Transfer
Application.Workbooks.Open ("C:\Users\Chris Treeman\My Documents\Dept MathCS     Financials\Accounting Files\Dept. Fund Tracking\Daily Compass Report Files\" & "CFNSPS02 - N16 SCIENCE HIRE INITIATIVE" & SpecifiedDate & ".xls")
While TWB.Worksheets("Import-Data").Cells(p, 3).Value <> "END"
On Error Resume Next
    TWB.Worksheets("Import-Data").Cells(p, Col) = Application.WorksheetFunction.VLookup(TWB.Worksheets("Import-Data").Cells(p, 3).Value, _
    Workbooks("CFNSPS02 - N16 SCIENCE HIRE INITIATIVE" & SpecifiedDate & ".xls").Worksheets("Sheet1").Range("$E$11:$BI$200"), 57, False)
    p = p + 1
Wend
Windows("CFNSPS02 - N16 SCIENCE HIRE INITIATIVE" & SpecifiedDate & ".xls").Close (False)


    'CFNSPS03 Data Transfer
Application.Workbooks.Open ("C:\Users\Chris Treeman\My Documents\Dept MathCS Financials\Accounting Files\Dept. Fund Tracking\Daily Compass Report Files\" & "CFNSPS03 - MICHELANGELO GRIGNI START-UP F" & SpecifiedDate & ".xls")
While TWB.Worksheets("Import-Data").Cells(q, 3).Value <> "END"
On Error Resume Next
    TWB.Worksheets("Import-Data").Cells(q, Col) = Application.WorksheetFunction.VLookup(TWB.Worksheets("Import-Data").Cells(q, 3).Value, _
    Workbooks("CFNSPS03 - MICHELANGELO GRIGNI START-UP F" & SpecifiedDate & ".xls").Worksheets("Sheet1").Range("$E$11:$BI$200"), 57, False)
    q = q + 1
Wend
Windows("CFNSPS03 - MICHELANGELO GRIGNI START-UP F" & SpecifiedDate & ".xls").Close (False)


    'CFNSPS04 Data Transfer
Application.Workbooks.Open ("C:\Users\Chris Treeman\My Documents\Dept MathCS Financials\Accounting Files\Dept. Fund Tracking\Daily Compass Report Files\" & "CFNSPS04 - CLS J Taylor" & SpecifiedDate & ".xls")
While TWB.Worksheets("Import-Data").Cells(r, 3).Value <> "END"
On Error Resume Next
    TWB.Worksheets("Import-Data").Cells(r, Col) = Application.WorksheetFunction.VLookup(TWB.Worksheets("Import-Data").Cells(r, 3).Value, _
    Workbooks("CFNSPS04 - CLS J Taylor" & SpecifiedDate & ".xls").Worksheets("Sheet1").Range("$E$11:$BI$200"), 57, False)
    r = r + 1
Wend
Windows("CFNSPS04 - CLS J Taylor" & SpecifiedDate & ".xls").Close (False)


    'CFNSPS05 Data Transfer
Application.Workbooks.Open ("C:\Users\Chris Treeman\My Documents\Dept MathCS Financials\Accounting Files\Dept. Fund Tracking\Daily Compass Report Files\" & "CFNSPS05 - Equipment - Taylor" & SpecifiedDate & ".xls")
While TWB.Worksheets("Import-Data").Cells(s, 3).Value <> "END"
On Error Resume Next
    TWB.Worksheets("Import-Data").Cells(s, Col) = Application.WorksheetFunction.VLookup(TWB.Worksheets("Import-Data").Cells(s, 3).Value, _
    Workbooks("CFNSPS05 - Equipment - Taylor" & SpecifiedDate & ".xls").Worksheets("Sheet1").Range("$E$11:$BI$200"), 57, False)
    s = s + 1
Wend
Windows("CFNSPS05 - Equipment - Taylor" & SpecifiedDate & ".xls").Close (False)


    'CFNSPS06 Data Transfer
Application.Workbooks.Open ("C:\Users\Chris Treeman\My Documents\Dept MathCS Financials\Accounting Files\Dept. Fund Tracking\Daily Compass Report Files\" & "CFNSPS06 - SOM Equipment" & SpecifiedDate & ".xls")
While TWB.Worksheets("Import-Data").Cells(t, 3).Value <> "END"
On Error Resume Next
    TWB.Worksheets("Import-Data").Cells(t, Col) = Application.WorksheetFunction.VLookup(TWB.Worksheets("Import-Data").Cells(t, 3).Value, _
    Workbooks("CFNSPS06 - SOM Equipment" & SpecifiedDate & ".xls").Worksheets("Sheet1").Range("$E$11:$BI$200"), 57, False)
    t = t + 1
Wend
Windows("CFNSPS06 - SOM Equipment" & SpecifiedDate & ".xls").Close (False)


    'CFNSPS07 Data Transfer
Application.Workbooks.Open ("C:\Users\Chris Treeman\My Documents\Dept MathCS Financials\Accounting Files\Dept. Fund Tracking\Daily Compass Report Files\" & "CFNSPS07 - JACKSON MATH AND SCIENCE FUND" & SpecifiedDate & ".xls")
While TWB.Worksheets("Import-Data").Cells(u, 3).Value <> "END"
On Error Resume Next
    TWB.Worksheets("Import-Data").Cells(u, Col) = Application.WorksheetFunction.VLookup(TWB.Worksheets("Import-Data").Cells(u, 3).Value, _
    Workbooks("CFNSPS07 - JACKSON MATH AND SCIENCE FUND" & SpecifiedDate & ".xls").Worksheets("Sheet1").Range("$E$11:$BI$200"), 57, False)
    u = u + 1
Wend
Windows("CFNSPS07 - JACKSON MATH AND SCIENCE FUND" & SpecifiedDate & ".xls").Close (False)

TWB.Sheets("Import-Data").Activate
Cells(6, CLoc).Select

TWB.Sheets("Dept. NSPs & SPPs").Activate
Application.Calculation = xlCalculationAutomatic
Columns(ActiveCell.Column).AutoFit

Else
    MsgBox ("Please select the correct cell for Import")
End If

Application.ScreenUpdating = True

End Sub

https://stackoverflow.com/questions/35135116/vba-excel-to-mac-file-path?rq=1