Loop through drop down and copy/paste values

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Okay, try this:

Sub RunDropdownScenarios()
    Dim cDD As Range
    Dim sDD() As String
    Dim i As Integer
    Dim sWS As String
    Dim ws As Worksheet
    
    On Error GoTo Terminate
    
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With
    
    With Worksheets("Before")
        Set cDD = .Range("O19")
        sDD = Split(cDD.Validation.Formula1, ",")
        For i = LBound(sDD) To UBound(sDD)
            cDD.Value = sDD(i)
            sWS = "After " & sDD(i)
            If SheetExists(sWS) Then
                Set ws = Worksheets(sWS)
            Else
                Set ws = Worksheets.Add(after:=Worksheets(Worksheets.Count))
                ws.Name = sWS
            End If
            .Range("C13:AF130").Copy
            ws.Range("B13").PasteSpecial xlPasteValuesAndNumberFormats
            ws.Range("B13").PasteSpecial xlPasteFormats
            Application.CutCopyMode = False
        Next i
    End With
Terminate:
    If Err Then
        Debug.Print "ERROR", Err.Number, Err.Description
        Err.Clear
    End If
    With Application
        .EnableEvents = True
        .ScreenUpdating = True
    End With
End Sub

Function SheetExists(ByRef sSheetName As String) As Boolean
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name = sSheetName Then
            SheetExists = True
            Exit Function
        End If
    Next ws
    SheetExists = False
End Function

Your source / destination copy and paste ranges don't seem quite consistent, in your sample workbook, so be careful of where you are copying and pasting...

Sure - you need to map options to sheets. Something like this:

Sub RunDropdownScenarios()
    Dim cDD As Range
    Dim sDD() As String
    Dim i As Integer
    Dim sWS() As String
    Dim ws As Worksheet
    
    On Error GoTo Terminate
    
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With
    
    sWS = Split("Dummysheet,Finance,Third Option,four,CINQ,hexy,LuckySeven,OCT,999,Last", ",")
    
    With Worksheets("Before")
        Set cDD = .Range("O19")
        sDD = Split(cDD.Validation.Formula1, ",")
        For i = LBound(sDD) To UBound(sDD)
            cDD.Value = sDD(i)
            If SheetExists(sWS(i)) Then
                Set ws = Worksheets(sWS(i))
            Else
                Set ws = Worksheets.Add(after:=Worksheets(Worksheets.Count))
                ws.Name = sWS(i)
            End If
            .Range("C13:AF130").Copy
            ws.Range("B13").PasteSpecial xlPasteValuesAndNumberFormats
            ws.Range("B13").PasteSpecial xlPasteFormats
            Application.CutCopyMode = False
        Next i
    End With
Terminate:
    If Err Then
        Debug.Print "ERROR", Err.Number, Err.Description
        Err.Clear
    End If
    With Application
        .EnableEvents = True
        .ScreenUpdating = True
    End With
End Sub

Function SheetExists(ByRef sSheetName As String) As Boolean
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name = sSheetName Then
            SheetExists = True
            Exit Function
        End If
    Next ws
    SheetExists = False
End Function