I want to extract multiple pieces of data from every file in a folder and bring it into a table in a new document.

The source files are data collection runs and are identically formatted. The easier copy and paste parts are numerical data values (F6:H6 and F7:H7).

The non-numerical values are all the other lines. I only want a portion of them.  

For example the data from A1 of the source file is the run title which is formatted: "Run ## - 'run description'", but I only want the run number.  

If I copy and paste it it comes out like "RUN 25 - Power Eval." when all I want is "25".

I've tried a handful of things. I'm sure there might be an easy way to use Mid but I couldn't figure it out.  

Each source file is analyzed using a macro so I also tried adding a named range named Runnum in each of the files that uses a function to extract the run number from the file name. The name range works, like if I put "=Runnum" in a cell of the source file it returns just the number i.e. "25", but I wasn't able to figure out how to call the value of the named range and extract it.

The other items that aren't easily copy and paste-able fall under the same issue. I should hopefully be able to apply the fix to the rest.

The values I am extracting the data from are all cells that contain formulas. This is why I was using PasteSpecial for the easy copy and paste lines, but I'm not sure if this will be a factor in the solution for the other lines.

```vb
Sub Summary()

    Dim xFd As FileDialog
    Dim xSPath As String
    Dim xXLSXFile As String       'file to process
    Dim wsTarget As Worksheet
    Dim wbSource As Workbook
    Dim wsSource As Worksheet
    Dim rowTarget As Long         'output row

    Application.ScreenUpdating = False  
    Application.DisplayAlerts = False 

    rowTarget = 8

    'reset application settings in event of error
    On Error GoTo errHandler
    Application.ScreenUpdating = False

    'xWsheet = ActiveWorkbook.Name
    Set xFd = Application.FileDialog(msoFileDialogFolderPicker)
    xFd.Title = "Select a folder:"
    If xFd.Show = -1 Then
        xSPath = xFd.SelectedItems(1)
    Else
        Exit Sub
    End If
    If Right(xSPath, 1) <> "\" Then xSPath = xSPath + "\"

    'set up the target worksheet
    Set wsTarget = Sheets(1)

    'loop through the Excel files in the folder
    xXLSXFile = Dir(xSPath & "*.xlsx")
    Do While xXLSXFile <> ""
        If xXLSXFile <> ThisWorkbook.Name Then
            Set wbSource = Workbooks.Open(xSPath & xXLSXFile)
            Set wsSource = wbSource.Worksheets(1)

            ''With wsTarget
                ''.Range("A" & rowTarget).Value = wsSource.Range("Runnum").Value
            ''End With

            With wsSource
                ''.Range("Runnum").Value = wsTarget.Range("A" & rowTarget).Value
                ''.Names("Runnum").RefersToRange.Value
                ''wsTarget.Range("B" & rowTarget).Value
                ''.Range("A1").Value = Mid(str, 5, InStr(str, " ") - 5).Copy
                ''wsTarget.Range("A" & rowTarget).PasteSpecial xlPasteValues 
                ''.Range("Runnum").Copy
                ''wsTarget.Range("A" & rowTarget).PasteSpecial xlPasteValues
                .Range("A1").Copy
                wsTarget.Range("A" & rowTarget).PasteSpecial xlPasteValues  'Run# 'remove first 4 characters and all characters after number/from the "-" -1
                .Range("B4").Copy
                wsTarget.Range("B" & rowTarget).PasteSpecial xlPasteValues
                .Range("F6:H6").Copy
                wsTarget.Range("C" & rowTarget).PasteSpecial xlPasteValues
                .Range("F7:H7").Copy
                wsTarget.Range("F" & rowTarget).PasteSpecial xlPasteValues
                .Range("D1").Copy
                wsTarget.Range("I" & rowTarget).PasteSpecial xlPasteValues  'remove first 7 characters
                .Range("B9").Copy
                wsTarget.Range("J" & rowTarget).PasteSpecial xlPasteValues
                .Range("B13").Copy
                wsTarget.Range("K" & rowTarget).PasteSpecial xlPasteValues  'remove every thing after the fist space -1
            End With
        End If

        'close the source workbook, increment the output row and get the next file
        wbSource.Close SaveChanges:=False
        rowTarget = rowTarget + 1
        xXLSXFile = Dir()
    Loop

errHandler:
    On Error Resume Next
    Application.ScreenUpdating = True

    Application.DisplayAlerts = True
    Application.ScreenUpdating = True

    Set wsSource = Nothing
    Set wbSource = Nothing
    Set wsTarget = Nothing
End Sub

```

I turned a few of the lines of code in the loop into comments with a '' just to show a few of the things I've tried. Really I kept them in to help me debug.