How do I take Excel files from my Chrome Downloads and simultaneously combine them as tabs in an Excel Workbook?

Question

Currently I have to manually click on each file in my Chrome Downloads and drag them into a workbook as a tab. If I download 60+ Excel files, it's a lot. Is it possible to have them all join together in one workbook simultaneously? Is there a code/formula/operation?

I tried "Get Data--> From Files--> From Folder and combine and load the files, but they don't combine as separate tabs. I just see general data of all the files in one worksheet tab.

Answer 1

Note: im operating under the assumption each workbook you are downloading is filetype ".xlsx" and contains only 1 worksheet.

If the files are in your "Chrome Downloads" then they are also located in your computers download folder. For me this folder is "C:\Users[your name]\Downloads". The first step is for you to figure out where these downloads are going.

Do these files have names following some sort of pattern? If yes this will make the automation simpler.

Below I will layout a simple directory traversal using

pattern matching to select multiple files.

Specifically, every file of type ".xlsx" will have it's first worksheet pulled into the workbook containing the macro.

Sub getDownloads()

    Application.DisplayAlerts = False
    Application.ScreenUpdating = False

    ' *** WHAT YOU NEED TO CHANGE ***
    Dim directory_Of_Downloads As String: directory_Of_Downloads = "C:\Users\[your name probably]\Downloads"

    Dim fileName As Variant: fileName = Dir(directory_Of_Downloads & "\*.xlsx", vbDirectory)

    Dim wb As Workbook

    While Not fileName = ""
        Set wb = Workbooks.Open(directory_Of_Downloads & "\" & fileName, ReadOnly:=True)
        wb.Sheets(1).Copy Before:=ThisWorkbook.Sheets(1)
    
        ' If the sheet name in the source workbook is a duplicate of a pre-existing worksheet in our current workbook
        ' then the sheet will end up being named "Sheet (#)"
        On Error Resume Next
        ThisWorkbook.Sheets(1).Name = wb.Sheets(1).Name
        On Error GoTo 0
    
        ' close the workbook
        wb.Close SaveChanges:=False
    
        'Delete the workbook -> If you are sure uncomment the below line
        'Kill directory_Of_Downloads & "\" & fileName
    
        ' set fileName = The next file located at directory_Of_Downloads which meets our search criteria -> ".xlsx"
        fileName = Dir
    Wend

    Application.DisplayAlerts = True
    Application.ScreenUpdating = True

End Sub

Best of luck!