VBA code to either copy/paste or reference a single cell ("AF2") from every wookbook in a folder

myfavoritecoloris 

You may tweak your code like this...

Sub GRABTOTAL()
    Dim xFd As FileDialog
    Dim xFdItem As Variant
    Dim xFileName As String
    Dim startRow As Long
    Dim wbSource As Workbook
    Dim wbDest As Workbook
    Dim wsDest As Worksheet
    
    Application.ScreenUpdating = False
    
    Set wbDest = ThisWorkbook   'The Workbook which contains this Macro
    
    Set wsDest = wbDest.Worksheets.Add(before:=wbDest.Worksheets(1))    'Sheet will be added to write the values from all the csv files
    wsDest.Range("A1").Value = "Totals"
    startRow = 2    'Write the values in column A starting from Row2
    
    Set xFd = Application.FileDialog(msoFileDialogFolderPicker)
    If xFd.Show = -1 Then
        xFdItem = xFd.SelectedItems(1) & Application.PathSeparator
        xFileName = Dir(xFdItem & "*.csv*")
        Do While xFileName <> ""
            Set wbSource = Workbooks.Open(xFdItem & xFileName)
            
            'Fetchnig the value from cell AF2 in column A of the newly added Sheet in this Workbook
            wsDest.Range("A" & startRow).Value = wbSource.Worksheets(1).Range("AF2").Value
            
            wbSource.Close False    'Closing the csv file without saving
            
            startRow = startRow + 1 'Incrementing the row by 1
            
            xFileName = Dir
        Loop
    End If
    Application.ScreenUpdating = True
End Sub