Copy and paste data through multiple workbooks

[CDaviess]

Hi,

I am looking for a VBA code to look through Multiple workbooks in a folder, look through a column, match to an inputted Date/Value then past the whole row where this matched date is into a Master book.
I currently have this only for a single workbook and it works but I keep getting an 1004 application-defined or object-defined error.
If anyone can see why that issue is happening and how i can spread this across multiple workbooks in a folder.

Many thanks in advance!

Sub Date_Filter()
Application.ScreenUpdating = False
Dim lastRow As Long
Dim lastrowa As Long
Dim counter As Long
Dim ns As String
ns = "Valves Due"
Dim C As Long
Dim s As Variant
C = 4
Dim i As Long
s = InputBox("Enter Value to search", , "Date")
Sheets.Add(After:=Sheets(Sheets.Count)).Name = ns

''Worksheets("Sheet").Range("A1:K3").Copy''
''Destination = Worksheets(Worksheets.Count).Range ("A1:K3")''

For i = 1 To Sheets.Count - 1
lastRow = Sheets(i).Cells(Rows.Count, C).End(xlUp).Row

With Sheets(i).Cells(1, C).Resize(lastRow)

lastrowa = Sheets(ns).Cells(Rows.Count, C).End(xlUp).Row + 1
.AutoFilter 1, s
counter = .Columns(C).SpecialCells(xlCellTypeVisible).Count
If counter > 1 Then
.Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).EntireRow.Copy Sheets(ns).Cells(lastrowa, 1)
Else
MsgBox "The value " & s & " Not Found On sheet named " & vbNewLine & Sheets(i).Name & vbNewLine & "Click OK and we will Continue"

End If
.AutoFilter
End With

Next
Application.ScreenUpdating = True
End Sub

 

[Joe4]

I currently have this only for a single workbook and it works but I keep getting an 1004 application-defined or object-defined error.

Does it give you the option to "Debug" when that error pops up?
If so, click it and tell us which line of code it highlights.

 

[CDaviess]

Does it give you the option to "Debug" when that error pops up?
If so, click it and tell us which line of code it highlights.

Thank you for your reply, I have actually now fixed this issue, on some of the workbooks there were empty sheets that I had missed. And when it did fault, it didn't highlight a line just had a pop-up.