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