Can anyone help me with the bold code that is a type mismatch below?
Sub Promise_Date_Change_Detector()
Application.ScreenUpdating = True
Dim WB As Workbook, WS As Worksheet
Dim c As Variant
Set fso = CreateObject("Scripting.FileSystemObject")
Set fldr = fso.GetFolder("U:\Public\All Customer Reports\All Customers Report 2018")
'Label Column Headers , "PO#", "Sales Order", "Line #", "Promise Date","file date"
Cells(5, 1).Value = "PO#"
Cells(5, 2).Value = "Sales Order"
Cells(5, 3).Value = "Line #"
Cells(5, 4).Value = "Promise Date"
'Build Input Box to Inquire as to PO in Question
PurchaseOrder = InputBox("What PO would you like information for?", "PO#?")
'Find Order Date in Latest file
LFile = Worksheets(1).Cells(2, 6).Value
Workbooks.Open (LFile)
k = Worksheets(1).Cells(Rows.Count, 1).End(xlUp).Row
For l = 2 To k
If Worksheets(1).Cells(l, 2).Value = PurchaseOrder Then
n = Worksheets(1).Cells(l, 14).Value
End If
Next l
o = n + 2
o = Format(o, "mm-dd-yyyy")
StrtFile = Application.WorksheetFunction.Concat(fldr, "", o, "am")
'Loop through all the files in the customers report folder and pull out the po info
x = ThisWorkbook.Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row + 1
For c = StrtFile To LFile
Application.DisplayAlerts = False
If fso.GetExtensionName(wbFile.Name) = "xls" Then
Set WB = Workbooks.Open(wbFile.Path)
Set WS = Worksheets(1)
wsLR = WS.Cells(Rows.Count, 1).End(xlUp).Row
For Y = 2 To wsLR
If WS.Cells(Y, 2).Value = PurchaseOrder Then
Z = 6
WS.Cells(Y, 2).Copy Destination:=ThisWorkbook.Worksheets(1).Cells(Z, 1)
WS.Cells(Y, 3).Copy Destination:=ThisWorkbook.Worksheets(1).Cells(Z, 2)
WS.Cells(Y, 4).Copy Destination:=ThisWorkbook.Worksheets(1).Cells(Z, 3)
WS.Cells(Y, 21).Copy Destination:=ThisWorkbook.Worksheets(1).Cells(Z, 4)
Z = Z + 1
End If
x = x + 1
Next Y
End If
WB.Close (wbFile.Path)
Application.DisplayAlerts = True
Next c
'concatenate po and promise date
'remove duplicate rows
Application.ScreenUpdating = True
End Sub
Sub Promise_Date_Change_Detector()
Application.ScreenUpdating = True
Dim WB As Workbook, WS As Worksheet
Dim c As Variant
Set fso = CreateObject("Scripting.FileSystemObject")
Set fldr = fso.GetFolder("U:\Public\All Customer Reports\All Customers Report 2018")
'Label Column Headers , "PO#", "Sales Order", "Line #", "Promise Date","file date"
Cells(5, 1).Value = "PO#"
Cells(5, 2).Value = "Sales Order"
Cells(5, 3).Value = "Line #"
Cells(5, 4).Value = "Promise Date"
'Build Input Box to Inquire as to PO in Question
PurchaseOrder = InputBox("What PO would you like information for?", "PO#?")
'Find Order Date in Latest file
LFile = Worksheets(1).Cells(2, 6).Value
Workbooks.Open (LFile)
k = Worksheets(1).Cells(Rows.Count, 1).End(xlUp).Row
For l = 2 To k
If Worksheets(1).Cells(l, 2).Value = PurchaseOrder Then
n = Worksheets(1).Cells(l, 14).Value
End If
Next l
o = n + 2
o = Format(o, "mm-dd-yyyy")
StrtFile = Application.WorksheetFunction.Concat(fldr, "", o, "am")
'Loop through all the files in the customers report folder and pull out the po info
x = ThisWorkbook.Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row + 1
For c = StrtFile To LFile
Application.DisplayAlerts = False
If fso.GetExtensionName(wbFile.Name) = "xls" Then
Set WB = Workbooks.Open(wbFile.Path)
Set WS = Worksheets(1)
wsLR = WS.Cells(Rows.Count, 1).End(xlUp).Row
For Y = 2 To wsLR
If WS.Cells(Y, 2).Value = PurchaseOrder Then
Z = 6
WS.Cells(Y, 2).Copy Destination:=ThisWorkbook.Worksheets(1).Cells(Z, 1)
WS.Cells(Y, 3).Copy Destination:=ThisWorkbook.Worksheets(1).Cells(Z, 2)
WS.Cells(Y, 4).Copy Destination:=ThisWorkbook.Worksheets(1).Cells(Z, 3)
WS.Cells(Y, 21).Copy Destination:=ThisWorkbook.Worksheets(1).Cells(Z, 4)
Z = Z + 1
End If
x = x + 1
Next Y
End If
WB.Close (wbFile.Path)
Application.DisplayAlerts = True
Next c
'concatenate po and promise date
'remove duplicate rows
Application.ScreenUpdating = True
End Sub