Loop to save all versions of report using drop down

Hello and thank you for any help I can get with this!

I am trying to use the following Macro to save all versions of a report, but I don't know how to create the loop. The reports value change based off of a drop down cell and I want to save every version of that drop down to a designated folder. My current Marco is causing the saved files to crash excel and I am not sure why.

My drop down is in cell C2 and it is the data validation is based on O16:O159

I am copy and pasting several tabs to a new worksheet because I need to save the report as values since I am currently using a cube connection that is not accessible by the end user.

Any help on this would be greatly appreciated!

This is the current Macro I am using:

Sub SaveAsValues()
    
    'Save Monthly Report as Values in new workbook
    Dim wkb As Excel.Workbook
    Dim newWB As Excel.Workbook
    Dim wks As Excel.Worksheet
    Dim newWks As Excel.Worksheet
    Dim sheets As Variant
    Dim varName As Variant
    '----------------------------------------------
    
    Application.DisplayAlerts = False
    
    'Define the names of worksheets to be copied.
    sheets = VBA.Array("FTE Variance (8)", "Mill Levy Summary (7)", "Spending Graph (6)", "Forecasting Tool (5)", "16-17 Full Year (4)", "16-17 YTD (3)", "16-17 Current Month (2)", "Remaining Balance (1)", "Monthly Report Cover Page")
    
    
    'Create referenece to the current Excel workbook and to the destination workbook.
    Set wkb = Excel.ThisWorkbook
    Set newWkb = Excel.Workbooks.Add
    
    ActiveWorkbook.SaveAs "Book1"
    
        Workbooks("Book1").Connections.AddFromFile _
        "C:\Users\PAUL_CYR\Documents\My Data Sources\fin-dmart-prod LAWSONDW GL SUMMARY CUBE.odc"
    
    For Each varName In sheets
    
        'Clear reference to the [wks] variable.
        Set wks = Nothing
        
        'Check if there is a worksheet with such name
        On Error Resume Next
        Set wks = wkb.Worksheets(VBA.CStr(varName))
        On Error GoTo 0
        
        'If worksheet with such neam is not found, those instructions are skipped.
        If Not wks Is Nothing Then
        
            'Copy this worksheet to a new workbook
            Call wks.Copy(newWkb.Worksheets(1))
            
            Application.CalculateUntilAsyncQueriesDone
            
            'Get the reference to the copy of this worksheet and paste
            'all its content as values
            Set newWks = newWkb.Worksheets(wks.Name)
            With newWks
                Call .Cells.Copy
                Call .Range("a1").PasteSpecial(Paste:=xlValues)
            End With
            
        End If
        
    Next varName

    ActiveWorkbook.SaveAs Filename:="H:\1_School Support\RBR FY17\Monthly Reports\October\" & Range("B1").Text & " - Monthly Report - October.xlsx"

    Application.DisplayAlerts = True
    
    
End Sub