I have a master file where the data of all Team Managers are saved. What Im trying to do is to separate each Team Managers' data into different workbook where it should have their name as the filename.
I created another tab in the master file called "Manager_List" - this contains the name of the managers as the Criteria from A2 to A10 as an example.
When I run my current script, it loops to the same Criteria which in A2 under the "Manager_List" tab. So it keeps on doing the same thing over and over again. After saving the first file, i want it to go to the next Criteria in A3, then A4, then A5 so on...
Please help!
Sub Macro3()
Do
'This part should autofilter the data based on the names in the "Manager_List" tab
Selection.AutoFilter Field:=2, Criteria1:=Worksheets("Manager_List").Range("A2").Value
'After it filters the data, it will copy and paste to a new workbook
Range("A1:W1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Workbooks.Add
ActiveSheet.Paste
Columns("A:A").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Columns("A:V").Select
Columns("A:V").EntireColumn.AutoFit
Application.DisplayAlerts = False
Sheets(Array("Sheet2", "Sheet3")).Select
Sheets("Sheet3").Activate
ActiveWindow.SelectedSheets.Delete
Application.DisplayAlerts = True
Range("A1").Select
'This part will save the new workbook that contains the data of specific team manager.
'After saving it should close the new workbook and go back to the master file
Dim Path As String
Dim filename As String
Path = "C:\Users \Documents\Sept Results_"filename = Range("A2")
ActiveWorkbook.SaveAs filename:=Path & filename & ".xlsb", FileFormat:=50
ActiveWindow.Close
'Id like it to run again to the next cell in (Criteria1:=Worksheets("Manager_List").Range("A2").Value) - so that should be A3 until it reaches the blank cell before it stops.
Loop Until ActiveCell.Value = ""
End Sub
Bookmarks