Trying to loop through multiple files in a folder. Keep getting run time error 13 type mismatch on the bold below. Can anyone help?
Rich (BB code):
Sub Promise_Date_Change_Detector()
Dim WB As Workbook, WS As Worksheets
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#?")
'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 Each wbFile In fldr.Files
If fso.GetExtensionName(wbFile.Name) = "xls" Then
Set WB = Workbooks.Open(wbFile.Path)
Set WS = Worksheets("Foglio1")
wsLR = WS.Cells(Rows.Count, 1).End(xlUp).Row
For Y = 2 To wsLR
If Cells(Y, 2).Value = PurchaseOrder Then
Cells(Y, 2).Copy
ActiveSheet.Cells(x, 1).Paste
Cells(Y, 3).Copy
ActiveSheet.Cells(x, 2).Paste
Cells(Y, 4).Copy
ActiveSheet.Cells(x, 3).Paste
Cells(Y, 21).Copy
ActiveSheet.Cells(x, 4).Paste
End If
x = x + 1
Next Y
End If
Next wbFile
'concatenate po and promise date
'remove duplicate rows
End Sub
Last edited by a moderator: