Here is what I ended up using.
Hope it helps.
Chunk
Sub UpdateFiles()
Dim wb As Workbook
Dim myPath As String
Dim myFile As String
Dim myExtension As String
Dim rng As Range
Dim wbrng As Range
Dim proj As String
Dim nwproj As String
Dim nwtype As String
Dim nwdate1 As String
Dim nwdate2 As String
Dim nwdate3 As String
Dim nwFile As String
Set rng = Worksheets("LookUpList").Range("D2:D40")
'Optimize Macro Speed
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
For Each cell In rng
If cell.Value = "Y" Then
'Target File Extension (must include wildcard "*")
myExtension = "*.csv"
proj = cell.Offset(0, -2)
myPath = "C:\ChunksStuff\datfiles\"
'Target Path with Ending Extention
myFile = Dir(myPath & "*" & proj & myExtension)
'Loop through each Excel file in folder
Do While myFile <> ""
'Set variable equal to opened workbook
Set wb = Workbooks.Open(myPath & myFile)
'Ensure Workbook has opened before moving on to next line of code
DoEvents
'Delete first column
With wb
If Range("A1") = "FileName" Then
Range("A:A").Select
Selection.Delete
Else
End If
End With
'Configure variables to rename file
nwproj = cell.Offset(0, 1)
If InStr(myFile, "_TL_") Then
nwtype = "_TL_"
ElseIf InStr(myFile, "_NN_") Then
nwtype = "_NN_"
ElseIf InStr(myFile, "_NU_") Then
nwtype = "_NU_"
End If
nwdate1 = Right(myFile, 13)
nwdate2 = Left(nwdate1, 5)
nwdate3 = Mid(nwdate1, 8, 9)
If InStr(myFile, "PPS") Then
nwFile = ("PO" & nwproj & nwtype & nwdate2 & nwdate3)
ElseIf InStr(myFile, "PWL") Then
nwFile = ("WO" & nwproj & nwtype & nwdate2 & nwdate3)
End If
'Save and Close Workbook
wb.SaveAs FileName:=myPath & "Converted\" & nwFile
wb.Close SaveChanges:=True
'Ensure Workbook has closed before moving on to next line of code
DoEvents
'Get next file name
myFile = Dir
Loop
End If
Next cell
'Message Box when tasks are completed
MsgBox "Task Complete!"
ResetSettings:
'Reset Macro Optimization Settings
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub