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:
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