Here is my dilemma. I have a master workbook with 5 sheets. Each sheet contains a different data-set, but will be filtered by a "Dealer Code" criteria that is common to all 5 sheets. This "Dealer Code" criteria is Column A in all 5 sheets. My goal is to apply autofilter to all 5 sheets, insert a dealer code criteria into a filter input box, copy the filtered data from each sheet, create a new workbook, prompt the Save As dialog box to open, and then paste the filtered data into 5 new sheets. I've had limited success with copying 1 tab and performing the rest of the functions, but not with all 5. I can get all 5 to paste to 1 new book and sheet, but I'm stuck here.
Sub AutoFilter_IHS() Dim My_Range As Range Dim FilterCriteria As String Dim rng As Range Dim sheetName As String Dim newBook As Excel.Workbook Dim Ws As Worksheet Dim i As Long Dim bFileSaveAs As Boolean Application.ScreenUpdating = False For Each Ws In ThisWorkbook.Worksheets Set My_Range = Range("A1:Z" & LastRow(ActiveSheet)) My_Range.Parent.Select My_Range.Parent.AutoFilterMode = False Exit For Next For Each Ws In ThisWorkbook.Worksheets Set My_Range = Range("A1:Z" & LastRow(ActiveSheet)) FilterCriteria = InputBox("Enter Dealer Code", "Dealer Selection") My_Range.AutoFilter Field:=1, Criteria1:="=" & FilterCriteria Exit For Next Workbooks.Add bFileSaveAs = Application.Dialogs(xlDialogSaveAs).Show If Not bFileSaveAs Then MsgBox "User Cancelled", vbCritical My_Range.Parent.AutoFilter.Range.Copy Selection.PasteSpecial Paste:=xlPasteAll Application.ScreenUpdating = True End Sub