How to combine multiple excel books into one using Power Query and split it again using VBA

This article will show you how to use Power Query to combine data stored in multiple Excel books into a single Excel book. In addition, VBA code for re-splitting the combined Excel book will also be presented.

Who should read this article

If you work at the head office and are following the steps below to receive budget requests from the various branches and respond with the allocated amounts, you should read this article.

Cut and paste data from each Excel book into one (Combine)
Enter the allocation amount for the requested amount
To reply to each branch, copy the combined Excel book as many as the number of branches and manually delete the data of other branches (Split)

Until 2016, it was necessary to write difficult code in VBA to combine data.

However, Power Query was introduced in 2016, and VBA is no longer required to combine data.

The advantage of Power Query over VBA is that it is easy to operate.

Now, read the preconditions and sample files first, as I will show you tutorials.

Preconditions and Sample files

Preconditions

The preconditions for the tutorial introduced this time are as follows:

All excel books are saved in one folder.
The sheet name of each excel book with data entered is the same.

Sample files

Download it here.

DOWNLOAD

After unzipping the sample file, there are Excel books named "01_branchA.xlsx", "02_branchB.xlsx", and "03_branchC.xlsx" in the folder "test."

Every workbook has a sheet named “data.”

Each Excel book has data as shown in the figure below.

0A_branchA.xlsx

02_branchB.xlsx

03_branchC.xlsx

Tutorials

Overview of tutorials

You work at the head office and want to combine the excel books of budget requests sent from each branch, enter the allocated amounts, and then split the excel books by branch.

In order to implement these tasks, I will show you the following three tasks with the actual screen.

Combine
Enter
Split

Combine using Power Query

First, let’s use Power Query to combine data stored in multiple Excel books.

Create a new Excel book and click "Get Data" at the left end of the tab "Data".

Then click “From File” and “From Folder.”

Open the unzipped folder.

Click "Combine & Load" from the pull-down menu of the "Combine" button at the bottom of the window.

Click "data" on the left side of the opened window, and click "OK" on the lower right side.

When the process is complete, you will see a table with the data from the three Excel books combined, as shown in the figure.

Once you learn how to do it, it's easier than cut-and-paste.

Enter the amount of allocation

Since the combined data is in a “table”, to enter additional data, simply enter it at the table end.

Let's enter "Allocation" in cell of E1.

When you enter text in E1, cells up to E10 are added to the table and their colors change.
Enter the amount you want to allocate from E2 to the cells E10.

I entered 90% of the requested amount as the allocated amount.

Next, let's split the excel book to reply to each branch.

Split using VBA

As far as I can tell, splitting an excel book is only possible with VBA.

Pressing Atl + F11 in Excel will open a VBA window.

Press Atl + F11 on the keyboard, and when the VBA window opens, click on "Module" in the menu "Insert".

As the standard module is added, copy and paste the code below.

Sub Split_Sht()
    myArr = Cells(1, 1).CurrentRegion
    maxRow = UBound(myArr)

    Set myDic = CreateObject("Scripting.Dictionary")
    For i = 2 To maxRow
            If Not myDic.Exists(myArr(i, 1)) Then
                myDic.Add myArr(i, 1), Null
            End If
    Next i
    mySourceName = myDic.Keys

    Worksheets(1).Cells(1, 1).Activate
    
    Worksheets(1).ListObjects(1).TableStyle = ""

    Dim myPath As String
    MsgBox "Select a folder for save", vbExclamation
        With Application.FileDialog(msoFileDialogFolderPicker)
        
            .InitialFileName = ThisWorkbook.Path &amp; "\"

            If .Show = True Then

                myPath = .SelectedItems(1)
            Else

                MsgBox "Canceled", vbExclamation
                Exit Sub
            End If
        End With

    For Each c In mySourceName

        Worksheets(1).Activate
        With ActiveSheet.Range("A1")
            .AutoFilter Field:=1, Criteria1:=c
            .CurrentRegion.Copy
        End With

        Set ws = Worksheets.Add(After:=Worksheets(Worksheets.Count))
        Selection.PasteSpecial Paste:=xlPasteColumnWidths
        ActiveSheet.Paste

        Columns("A:A").Delete

        ws.Name = "data"
        ActiveSheet.Move
        ActiveSheet.Cells(1, 1).Select
        ActiveWorkbook.SaveAs myPath &amp; c
        ActiveWorkbook.Close
    Next
    With Worksheets(1)
        .Activate
        .ShowAllData
    End With
    Set myDic = Nothing
End Sub