I have managed put a code together. It is working, but it’s not very sufficient as I would need to create a 20 macros and rename the filtered text for each macro. I have two workbooks Q4 where the code saved (Q4 – cell A1:A20 filter text & B1:B20 file name) and and AA workbook where everything happening. How can I create it loops through the workbook Q4 and also the filter selects the range form workbook Q4?
''''' Sub Delete_Rows() Dim wb As Workbook Dim ws As Worksheet Dim Path As String Dim Filename As String Dim rng As Range Dim lastRow As Long Path = "C:Usersjam_jamDesktopABCABC1" '''Saves file Workbooks.Open ("C:Usersjam_jamDesktopABCAA.xlsx") '''Opens work book Set ws = ActiveWorkbook.Sheets("Sheet1") lastRow = ws.Range("D" & ws.Rows.Count).End(xlUp).Row Set rng = ws.Range("D1:D" & lastRow) '''''''' filter and delete all but header row With rng .AutoFilter Field:=4, Criteria1:="<>*ABCD*" ''''i would like to filter by range from workbook Q4 .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete End With ws.Name = ws.Range("D2") ActiveSheet.Range("$A$1:$N$3000").AutoFilter Field:=4 Range("M2").Select Filename = Workbooks("Q4.xlsm").Worksheets("333").Range("E13") ActiveWorkbook.SaveAs Filename:=Path & Filename & ".xlsx", FileFormat:=xlOpenXMLWorkbook ActiveWorkbook.Close End Sub
Thank you for any help?