Hi,

I can't figure out why this isn't working. Everything works fine but the second to last line gives me an error message saying Method of Range_Global failed. I'm guessing it has something to do with the variable sDataName1, because it was working fine until I added that.

There is more code after what you see here that completes the loop but I just thought it would be easier to include only this code.

I think it's a scope issue, but I just don't understand it. I'm looking more for an explanation than substituted code so that I can try to figure it out on my own. Your help is very appreciated!

Sub FilterData()

Range("AllData[REC Manager]").Copy
Sheets("MAP").Range("A2").PasteSpecial Paste:=xlPasteValues
Range("A1").Select
ActiveSheet.ListObjects.Add(xlSrcRange, Range(Selection, Selection.End(xlDown)), , xlYes).Name = _
"RecMgrList"
ActiveSheet.ListObjects("RecMgrList").TableStyle = "Table Style 1"
    
'Remove duplicates
Range("RecMgrList").RemoveDuplicates Columns:=1, Header:=xlYes
    
    ActiveWorkbook.Worksheets("MAP").ListObjects("RecMgrList"). _
        Sort.SortFields.Add Key:=Range("A2"), SortOn:=xlSortOnValues, Order:= _
        xlAscending, DataOption:=xlSortTextAsNumbers
    With ActiveWorkbook.Worksheets("MAP").ListObjects("RecMgrList").Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
'Variables to create manager sheets and tables
Dim sMgrNamesR As String, sDataName1 As String, iCntR_Mgr As Integer, iCntrR_Mgr As Integer
    iCntR_Mgr = Application.CountA(Range("RecMgrList"))
    For iCntrR_Mgr = 2 To iCntR_Mgr
        sMgrNamesR = "Mgr - " & Cells(iCntrR_Mgr, "A").Value
        sDataName1 = Cells(iCntrR_Mgr, "A").Value
            
            'Create a sheet and table in the name of sMgrNames
            Sheets.Add before:=Sheets(2)
            ActiveSheet.Name = sMgrNamesR
            
                Sheets("All").Select
                Cells.Copy
                Sheets(sMgrNamesR).Activate
                Sheets(sMgrNamesR).Paste
                    Application.CutCopyMode = False
                With ActiveSheet
                    .Range("A2").ListObject.Name = sMgrNamesR
                End With
                
                'Clear all data where sMgrNames is not the REC Mgr
                ActiveSheet.ListObjects(sMgrNamesR).Range.AutoFilter Field:=9, Criteria1:="<>" & sDataName1
                    Range(sMgrNamesR).EntireRow.Delete
                    ActiveSheet.ShowAllData
Thanks!