Loop type mismatch error

katlong

New Member
Joined
Sep 6, 2018
Messages
9
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:

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

MickG

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
Try
Code:
Dim WS As Worksheet

NB:- Worksheet not Worksheets
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,655
Office Version
  1. 365
Platform
  1. Windows
This
Code:
Dim WB As Workbook, WS As [COLOR=#ff0000]Worksheets[/COLOR]
should be Worksheet (singular)
 

Watch MrExcel Video

Forum statistics

Threads
1,109,533
Messages
5,529,397
Members
409,870
Latest member
Well59
Top