This code is working perfectly, but it saves to the desktop instead of path of the original. Also, I want to add the timestamp if possible in the file name? This will close out the project for me - so any help is so greatly appreciated!! THANK YOU! :)
Sub filter()Application.ScreenUpdating = FalseDim x As RangeDim rng As RangeDim rng1 As RangeDim last As LongDim sht As StringDim newBook As Excel.WorkbookDim Workbk As Excel.Workbook'Specify sheet name in which the data is storedsht = "This Sheet"'Workbook where VBA code residesSet Workbk = ThisWorkbook'change filter column in the following codelast = Workbk.Sheets(sht).Cells(Rows.Count, "Q").End(xlUp).RowWith Workbk.Sheets(sht)Set rng = .Range("A1:Q" & last)End WithWorkbk.Sheets(sht).Range("Q1:Q" & last).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("AA1"), Unique:=True' Loop through unique values in columnFor Each x In Workbk.Sheets(sht).Range([AA2], Cells(Rows.Count, "AA").End(xlUp))With rng.AutoFilter.AutoFilter Field:=17, Criteria1:=x.Value.SpecialCells(xlCellTypeVisible).Copy'Add New Workbook in loopSet newBook = Workbooks.Add(xlWBATWorksheet)newBook.Sheets.Add(After:=Sheets(Sheets.Count)).Name = x.ValuenewBook.ActivateActiveSheet.PasteEnd With'Save new workbooknewBook.SaveAs x.Value & ".xlsx"'Close workbooknewBook.Close SaveChanges:=FalseNext x' Turn off filterWorkbk.Sheets(sht).AutoFilterMode = FalseWith Application.CutCopyMode = False.ScreenUpdating = TrueEnd WithEnd Sub