VBA Code to sort tables and sum

[ryancgarrett]

I have around 170 xml files and I wrote this code to import each file into a separate sheet. After the xml table is imported I have it remove duplicates, filter so only 6 rows are shown and then sum those 6 showing values. It works some of the time but is a little buggy. I haven't coded for years and know this is sloppy, but any help is much appreciated.

Sub importXML()Dim nextName As String, I As Integer, fileName As String


For I = 1 To 170
nextName = Sheets("Roster Info").Cells(I, 1).Value
fileName = nextName & ".xml"


Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = nextName


    Sheets(nextName).Select
    
    On Error Resume Next
    ActiveWorkbook.XmlImport URL:="C:\Users\rgarret7\Desktop\Unit 2 Survey\" & fileName, _
    importmap:=Nothing, overwrite:=True, Destination:=Range("$A$1")
    
    Sheets(nextName).Range("A1:N217").RemoveDuplicates Columns:=5, Header:= _
    xlYes
    
    Columns("G:G").Select
    Selection.Insert Shift:=xlToRight
    Selection.NumberFormat = "0.00"
    Range("G2").Select
    ActiveCell.FormulaR1C1 = "=LEFT([@answer],1)"
    Range("G2").Select
        
    ActiveSheet.ListObjects("Table" & I).Range.AutoFilter Field:=5, Criteria1:= _
    Array("13", "20", "26", "31", "35", "4"), Operator:=xlFilterValues
    
    Range("G38").Select
    ActiveCell.FormulaR1C1 = _
        "=R[-213]C+R[-204]C+R[-197]C+R[-191]C+R[-186]C+R[-182]C"
    Range("G38").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    
    
Next I


End Sub