(Solved) – Open filter on range from another workbook delete rename tab save as from another workbook x20


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

Filename = Workbooks("Q4.xlsm").Worksheets("333").Range("E13")
ActiveWorkbook.SaveAs Filename:=Path & Filename & ".xlsx", FileFormat:=xlOpenXMLWorkbook

End Sub

Thank you for any help?

Leave a Reply

Your email address will not be published. Required fields are marked *