Merge Sheets from Multiple Excel Workbooks into One Workbook (VBA)

This is a continuation from Excel Hack 65. There, I showed how to consolidate data from multiple workbooks into one worksheet, in a new workbook.

In this Hack, I will show how to consolidate sheets from multiple workbooks into one new workbook.

While maintaining the workbooks from the previous tutorial...

1. Create a new spreadsheet in Excel, and name it "Consolidate1" (you can use any name you like, but I have used Consolidate1 so you can identify where it will be referred to in the code, and replace as required). Rename the worksheet to "Start"

2. insert the below code into a new module in the Visual Basic Editor (see how to activate the Developer Tab and open VBA — how to activate the Developer Tab and access the VBA Editor):

Option Explicit
Sub ConsolidationSheets()

Dim CurrentBook As Workbook
Dim IndvFiles As FileDialog
Dim FileImp As Long

'STEP 1: CREATE A DIALOG BOX TO SELECT WORKBOOKS TO CONSOLIDATE

Set IndvFiles = Application.FileDialog(msoFileDialogOpen)
With IndvFiles
.AllowMultiSelect = True
.Title = "Select workbooks for consolidation"
.ButtonName = ""
.Show
End With

'STEP 2: TURN OFF SCREEN UPDATES TO REDUCE SCREEN FLICKERS DURING THE CONSOLIDATION PROCESS

Application.ScreenUpdating = False

'STEP 3: CREATE A FOR-NEXT LOOP TO WORK THROUGH EACH WORKBOOK SELECTED IN STEP 1, AND …

For FileImp = 1 To IndvFiles.SelectedItems.Count
Set CurrentBook = Workbooks.Open(IndvFiles.SelectedItems(FileImp))

CurrentBook.Sheets("Sheet1").Select

'STEP 3A: ...COPY THE WORKSHEET, AND PASTE IT INTO THE NEW WORKBOOK
Sheets("Sheet1").Copy After:=Workbooks("Consolidate1.xlsm").Sheets(1)
Sheets("Sheet1").Select

'STEP 3B: ...RENAME THE COPIED WORKSHEET TO THE STATE NAME, FOR EASE OF REFERENCE
Sheets("Sheet1").Name = Range("C3").Text

'STEP 4: CLOSE THE WORKBOOK IN VIEW
CurrentBook.Close

Next FileImp

Application.ScreenUpdating = True

End Sub

3. Insert a new button control on the Consolidation sheet worksheet (Developer>Insert>Form Controls), assign the above macro (“ConsolidationSheets”) to it, and rename the button to “Consolidate worksheets"

4. Click the Consolidate worksheets button to run the code