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

(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
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?

Leave a Reply

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