Hello, as title suggests I need help on implementing a Macro that would autofilter loop trough my files and their sheets in a set folder path. The action it would perform would:
- The first step would be to open the file and autofilter a table. The column it would autofilter from is the 11 (Field) column / the last column in the table.
- The criteria for the filtering would be this symbol: "-".
- The workbooks also contain sheets that don't have any tables so it would probably need also to ignore them or skip trough if no table is found.
- Following that it would move to next sheet and perform the same action until done with all the sheets in the workbook. When done move to next workbook until every (Around 50) Excel workbooks are done.
This is for invoice attachments, so It's important there are no extra "empty rows" and that their filtered. This would save me so much time. I'm a beginner at this and would appreciate any help towards this project . Thank you in advance
Here's some relevant code that could help. Maybe combing these to solves the problem (I've tested the for each tbl in .Listobjects, but it did not work:
Code: Autofiltering code
Sub Autofilter()
ActiveSheet.ListObjects(1).Range.Autofilter Field:=10, Criteria1:="-"
End Sub
Code: This is the base for the filtering code would be on on line 18
Sub filter_Test ()
Application.ScreenUpdating = False
Dim srcWB As Workbook, FolderName As String, ws As Worksheet, shArr As Variant, tbl As ListObject
shArr = Array("Sheet1", "Sheet2", "Sheet3")
sPath = "C:\Desktop\Excel_Files_1\"
strExtension = Dir("*.xlsx")
Do While strExtension <> ""
Set srcWB = Workbooks.Open(sPath & strExtension)
For Each ws In Sheets
If InStr(1, Join(shArr, "|"), ws.Name) = False Then
With ws
'I was thinking the filtering would happen here
End With
End If
Next ws
Application.DisplayAlerts = False
End If
Next i
ActiveWorkbook.SaveAs Filename:=foldername2 & ActiveWorkbook.Name
Application.DisplayAlerts = True
srcWB.Close False
strExtension = Dir
Loop
Application.ScreenUpdating = True
End Sub
Display More