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

Hi and welcome to the forum.

1.) You need to edit your post and add CODE tags around your VBA code. It's a forum rule, and others are adamant about enforcing it.

2.) "My current Marco is causing the saved files to crash excel"
Can you better explain exactly what happens? It can help diagnose the issue. If the code errors, what is the error description and what line of code is highlighted when you click the Debug button on the error dialog?

3.) Delete the two Call commands.

With newWks
Call .Cells.Copy
Call .Range("a1").PasteSpecial(Paste:=xlValues)
End With

2.) Describe "crash". What do you see; Does it lockup, does it exit excel?

3.) Remove the parenthesis
.Range("a1").PasteSpecial(Paste:=xlValues)

Try this (not tested).

Sub SaveAsValues()
    
    'Save Monthly Report as Values in new workbook
    Dim newWB As Excel.Workbook
    Dim wks As Excel.Worksheet
    Dim newWks As Excel.Worksheet
    Dim vSheets As Variant
    Dim varName As Variant
    '----------------------------------------------
    
    Application.DisplayAlerts = False
    
    'Define the names of worksheets to be copied.
    vSheets = 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 destination workbook.
    Set newWkb = Excel.Workbooks.Add
    
        newWkb.Connections.AddFromFile _
        "C:\Users\PAUL_CYR\Documents\My Data Sources\fin-dmart-prod LAWSONDW GL SUMMARY CUBE.odc"
    
    For Each varName In vSheets
    
        'Clear reference to the [wks] variable.
        Set wks = Nothing
        
        'Check if there is a worksheet with such name
        On Error Resume Next
        Set wks = ThisWorkbook.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
            wks.Copy After:=newWkb.Worksheets(1)
            
            Application.CalculateUntilAsyncQueriesDone
            
            'Get the reference to the copy of this worksheet and paste
            'all its content as values
            With newWkb.Worksheets(wks.Name)
                .UsedRange.Value = .UsedRange.Value
            End With
            
        End If
        
    Next varName
    
    newWkb.SaveAs Filename:="H:\1_School Support\RBR FY17\Monthly Reports\October\" & Range("B1").Text & " - Monthly Report - October.xlsx"
    
    Application.DisplayAlerts = True
    
End Sub

That actually worked?

Need to step away for a while. I'll take a look at looping in a couple of hours.

The code (both old and new) doesn't use the value in the C2 DV list. So what changes in the code for the loop?

What file name do you want to save each version in the loop?

Sub SaveAsValues()
    
    'Save Monthly Report as Values in new workbook
    Dim newWB As Excel.Workbook
    Dim wks As Excel.Worksheet
    Dim vSheets As Variant
    Dim varName As Variant
    Dim DVItem As Range
    Dim counter As Long
    '----------------------------------------------
    
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    
    'Define the names of worksheets to be copied.
    vSheets = 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")
                        
    
    For Each DVItem In ThisWorkbook.sheets("Config").Range("O16:O159").Value            ' Loop DV list
    
        ThisWorkbook.sheets("Config").Range("C2").Value = DVItem.Value
    
        'Create referenece to the destination workbook.
        Set newWkb = Excel.Workbooks.Add
        
        newWkb.Connections.AddFromFile _
            "C:\Users\PAUL_CYR\Documents\My Data Sources\fin-dmart-prod LAWSONDW GL SUMMARY CUBE.odc"
        
        For Each varName In vSheets
        
            'Clear reference to the [wks] variable.
            Set wks = Nothing
            
            'Check if there is a worksheet with such name
            On Error Resume Next
            Set wks = ThisWorkbook.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
                wks.Copy After:=newWkb.Worksheets(1)
                
                Application.CalculateUntilAsyncQueriesDone
                
                'Get the reference to the copy of this worksheet and paste
                'all its content as values
                With newWkb.Worksheets(wks.Name).UsedRange
                    .Value = .Value
                End With
                
            End If
            
        Next varName
        
        newWkb.SaveAs Filename:="H:\1_School Support\RBR FY17\Monthly Reports\October\" & Range("B1").Text & " - Monthly Report - October.xlsx"
        newWkb.Close SaveChanges:=False
        counter = counter + 1
    
    Next DVItem
    
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    
    MsgBox counter & " reports saved. ", , "Monthly Reports Complete"
    
End Sub