Copy Specific Columns from a Filtered Range

Try this:

' K64's Module Code  - Uses AutoFilter method
Sub Export_Dumps_2()
Application.Calculation = xlCalculationManual 'Set calculation to manual to speed up processing and avoid recalculation errors
On Error Resume Next 'make sheets if they don't exist
Sheets.Add.Name = "Prod"
Sheets.Add.Name = "Innov"
On Error GoTo 0
With Sheets("Raw_Data").UsedRange
    .AutoFilter 12, "<>#N/A"
    .AutoFilter 23, "<>Innov Only"
    Sheets("Prod").Cells.Clear
    Intersect(.Rows, .Range("P5:P10000")).Copy Sheets("Prod").Range("B1")
    Intersect(.Rows, .Range("F5:F10000")).Copy Sheets("Prod").Range("C1")
    Sheets("Innov").Cells.Clear
    .AutoFilter 23, "<>Prod Only"
    Intersect(.Rows, .Range("P5:P10000")).Copy Sheets("Innov").Range("B1")
    Intersect(.Rows, .Range("F5:F10000")).Copy Sheets("Innov").Range("C1")
    .AutoFilter
End With
With Sheets("Prod")
    .Range("A1:A" & .Range("B50000").End(xlUp).Row).Value = "Add"
    .SaveAs ThisWorkbook.Path & "\" & .Name & ".csv", xlCSV
End With
With Sheets("Innov")
    .Range("A1:A" & .Range("B50000").End(xlUp).Row).Value = "Add"
    .SaveAs ThisWorkbook.Path & "\" & .Name & ".csv", xlCSV
End With
Application.Calculation = xlCalculationAutomatic
End Sub