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!
Bookmarks