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.
Code
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
Display More