Paste the contents of several folders into a spreadsheet

Paste the CONTENTS of those folders into individual columns, or paste a LISTING of the filenames in said folders?

These three macros work together to accomplish this task. Drop them all into a new Module, edit the initialfilename in the StartingMacro so that it opens to a folder closer to where you expect to start. Then run that macro, it will use the other two macros to loop through every folder in the selected folder and run the HyperlinkFiles macro on each folder separately.

Option Explicit
Dim wsList As Worksheet, Col As Long, Ext As String, AddLinks As Boolean

Sub StartingMacro()
Dim calcmode As Long, fPATH As String

Application.ScreenUpdating = False      ' Set various application properties.

'Select folder
    With Application.FileDialog(msoFileDialogFolderPicker)
        .AllowMultiSelect = False
        .InitialFileName = "C:\2013\"
        .Show
        If .SelectedItems.Count > 0 Then
            fPATH = .SelectedItems(1)
        Else
            Exit Sub
        End If
    End With

'Select the file type to list
    Ext = Application.InputBox("What kind of files? Type the file extension to collect" _
            & vbLf & vbLf & "(Example:  jpg, tif, gif, bmp, *)", "File Type", "tif", Type:=2)

    If Ext = "False" Then Exit Sub

'Option to create hyperlinks
    AddLinks = MsgBox("Add hyperlinks to the file listing?", vbYesNo) = vbYes

' Add a new worksheet
    Set wsList = Sheets.Add(After:=Sheets(Sheets.Count))
    Col = 1
    wsList.Cells(1, Col) = fPATH

    Call LoopController(fPATH)      'starts the loop feeding in the main folder

wsList.Columns.AutoFit
Application.ScreenUpdating = True   ' Restore the application properties.

End Sub


Private Sub LoopController(sSourceFolder As String)
'This will loop into itself, first processing the files in the folder
'then looping into each subfolder deeper and deeper until all folders processed
Dim Fldr As Object, FL As Object, SubFldr As Object

    Call HyperlinkFiles(sSourceFolder & Application.PathSeparator)

    Set Fldr = CreateObject("scripting.filesystemobject").Getfolder(sSourceFolder)
    For Each SubFldr In Fldr.SubFolders
        LoopController SubFldr.Path
    Next

End Sub

Sub HyperlinkFiles(fPATH As String)
'Author:    Jerry Beaucaire, ExcelForum.com
'Date:      10/8/2010
'Summary:   User selects a folder and file type, macro returns
'           a complete listing of all files matching that type
'           with a hyperlink to the file for ease of opening
Dim fname As String, NR As Long

Col = Col + 1
NR = 2

    With wsList
        .Cells(1, Col) = fPATH

        fname = Dir(fPATH & "*." & Ext)

        Do While Len(fname) > 0
            .Cells(NR, Col) = fname
            If AddLinks Then .Hyperlinks.Add Anchor:=.Cells(NR, Col), _
                Address:=fPATH & fname, TextToDisplay:=fname
          'set for next entry
            NR = NR + 1
            fname = Dir
        Loop

    End With

End Sub

Only way I can review what you've done in your workbook is to see the workbook.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic


Make sure all that code above goes into a single Module (Insert > Module)

You're very welcome.