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…
- 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 (click here to see how to access the Visual Basic 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
