Loop to run through data validation list with multiple lists

[Nick22Kelly]

Hi All, I have an excel file setup with multiple teams of people. I have 1 tab with 1 dropdown list with the complete population of people. There are however 9 different teams. I'm trying to make a loop that will change the data validation list for each specific team, save the statement as a pdf and save it to their respective manager's folder. The below code is what i have but i keep getting a 400 error. Let me know what you think.

Sub LoopByManager()

 Dim inputRangea As Range
 Dim inputRangeb As Range
 Dim inputRangec As Range
 Dim inputRanged As Range
 Dim inputRangee As Range
 Dim inputRangef As Range
 Dim inputRangeg As Range
 Dim inputRangeh As Range
 Dim inputRangei As Range
 Dim a As Range
 Dim SaveAsStr As String
 'Determine where validation comes from
 Set inputRangea = Evaluate(Sheets("Teams").Range("A3").Validation.Formula1)
 Set inputRangeb = Evaluate(Sheets("Teams").Range("B3").Validation.Formula1)
 Set inputRangec = Evaluate(Sheets("Teams").Range("C3").Validation.Formula1)
 Set inputRanged = Evaluate(Sheets("Teams").Range("D3").Validation.Formula1)
 Set inputRangee = Evaluate(Sheets("Teams").Range("E3").Validation.Formula1)
 Set inputRangef = Evaluate(Sheets("Teams").Range("F3").Validation.Formula1)
 Set inputRangeg = Evaluate(Sheets("Teams").Range("G3").Validation.Formula1)
 Set inputRangeh = Evaluate(Sheets("Teams").Range("H3").Validation.Formula1)
 Set inputRangei = Evaluate(Sheets("Teams").Range("I3").Validation.Formula1)
 'Begin Amy Drake Loop
 
 Application.ScreenUpdating = False
 For Each a In inputRangea
 Sheets("Statement - Template").Range("C3").Value = a
 SaveAsStr = ActiveWorkbook.Path & "\" & Sheets("Statement - Template").Range("C3").Value
 Sheets("Statement - Template").ExportAsFixedFormat Type:=xlTypePDF, _
 Filename:=SaveAsStr & " - Sales Support Statement" & ".pdf", _
 OpenAfterPublish:=False
 Next a
 
 'Begin Amy Ratliff Loop
 
 For Each a In inputRangeb
 Sheets("Statement - Template").Range("C3").Value = a
 SaveAsStr = ActiveWorkbook.Path & "\" & Sheets("Statement - Template").Range("C3").Value
 Sheets("Statement - Template").ExportAsFixedFormat Type:=xlTypePDF, _
 Filename:=SaveAsStr & " - Sales Support Statement" & ".pdf", _
 OpenAfterPublish:=False
 Next a
 
 'Begin Chad Davison Loop
 
 For Each a In inputRangec
 Sheets("Statement - Template").Range("C3").Value = a
 SaveAsStr = ActiveWorkbook.Path & "\" & Sheets("Statement - Template").Range("C3").Value
 Sheets("Statement - Template").ExportAsFixedFormat Type:=xlTypePDF, _
 Filename:=SaveAsStr & " - Sales Support Statement" & ".pdf", _
 OpenAfterPublish:=False
 Next a
 'Begin Daniel Ulrich Loop
 
 For Each a In inputRanged
 Sheets("Statement - Template").Range("C3").Value = a
 SaveAsStr = ActiveWorkbook.Path & "\" & Sheets("Statement - Template").Range("C3").Value
 Sheets("Statement - Template").ExportAsFixedFormat Type:=xlTypePDF, _
 Filename:=SaveAsStr & " - Sales Support Statement" & ".pdf", _
 OpenAfterPublish:=False
 Next a
 'Begin Freida Haselden Loop
 
 For Each a In inputRangee
 Sheets("Statement - Template").Range("C3").Value = a
 SaveAsStr = ActiveWorkbook.Path & "\" & Sheets("Statement - Template").Range("C3").Value
 Sheets("Statement - Template").ExportAsFixedFormat Type:=xlTypePDF, _
 Filename:=SaveAsStr & " - Sales Support Statement" & ".pdf", _
 OpenAfterPublish:=False
 Next a
 'Begin James Lubeck Loop
 
 For Each a In inputRangef
 Sheets("Statement - Template").Range("C3").Value = a
 SaveAsStr = ActiveWorkbook.Path & "\" & Sheets("Statement - Template").Range("C3").Value
 Sheets("Statement - Template").ExportAsFixedFormat Type:=xlTypePDF, _
 Filename:=SaveAsStr & " - Sales Support Statement" & ".pdf", _
 OpenAfterPublish:=False
 Next a
 
 'Begin Jon Michelsen Loop
 
 For Each a In inputRangeg
 Sheets("Statement - Template").Range("C3").Value = a
 SaveAsStr = ActiveWorkbook.Path & "\" & Sheets("Statement - Template").Range("C3").Value
 Sheets("Statement - Template").ExportAsFixedFormat Type:=xlTypePDF, _
 Filename:=SaveAsStr & " - Sales Support Statement" & ".pdf", _
 OpenAfterPublish:=False
 Next a
 
 'Begin Gene Stroman Loop
 
 For Each a In inputRangeh
 Sheets("Statement - Template").Range("C3").Value = a
 SaveAsStr = ActiveWorkbook.Path & "\" & Sheets("Statement - Template").Range("C3").Value
 Sheets("Statement - Template").ExportAsFixedFormat Type:=xlTypePDF, _
 Filename:=SaveAsStr & " - Sales Support Statement" & ".pdf", _
 OpenAfterPublish:=False
 Next a
 
 'Begin Ray Oswald Loop
 
 For Each a In inputRangei
 Sheets("Statement - Template").Range("C3").Value = a
 SaveAsStr = ActiveWorkbook.Path & "\" & Sheets("Statement - Template").Range("C3").Value
 Sheets("Statement - Template").ExportAsFixedFormat Type:=xlTypePDF, _
 Filename:=SaveAsStr & " - Sales Support Statement" & ".pdf", _
 OpenAfterPublish:=False
 Next a
 Application.ScreenUpdating = True

 End Sub

 

[mse330]

Welcome to Mr. Excel !

Which line is giving you the error ? Also, I think you can use 1 more loop to reduce the code like below

Sub LoopByManager()
Dim a As Range, InputRg As Range, SaveAsStr As String
Application.ScreenUpdating = False
For x = 1 To 9
    Set InputRg = Evaluate(Sheets("Teams").Cells(3, x).Validation.Formula1)
        For Each a In InputRg
            Sheets("Statement - Template").Range("C3").Value = a
            SaveAsStr = ActiveWorkbook.Path & "\" & Sheets("Statement - Template").Range("C3").Value
            Sheets("Statement - Template").ExportAsFixedFormat Type:=xlTypePDF, _
                Filename:=SaveAsStr & " - Sales Support Statement" & ".pdf", OpenAfterPublish:=False
        Next a
Next x
Application.ScreenUpdating = True
End Sub