Excel VBA is an integral a part of Excel automation, and VBA’s utilization and advantages cannot be undermined. When you’re in an uphill battle attempting to consolidate a number of sheets and workbooks in Excel, we’re right here to assist.
The macros talked about on this information will aid you obtain the seemingly insurmountable job in a matter of seconds (or minutes, if the info belongings are giant).
By following this tutorial, you will create your individual VBA macro in Excel and effectively merge a number of sheets into one single file.
Merging A number of Excel Sheets Into One File
For this job, the info is saved within the following sheets:
The sheet names listed above are for illustration functions solely. This VBA macro is generic and doesn’t rely on the sheet names; you’ll be able to customise the code to make use of it with any sheet title(s).
Pre-Requisites for Operating the Code
There are some conditions for operating the VBA code listed under.
You’ll want to retailer the macro code in a brand new Excel file. Save this workbook with a .xlsm extension. It can save you the VBA macro workbook with any title.
Open a brand new Excel file; press Alt + F11 in your keyboard to open the Excel VBA editor. As soon as the editor opens, add a brand new code module by clicking on the Insert tab on the prime. Choose Module to insert a brand new module; that is the place you will be coming into the VBA macro code given under.
The information sheets to be consolidated must be in one other separate workbook altogether. The title of the workbook and sheets may be no matter you select.
As quickly as you execute the VBA code, the VBA macro will cycle by every accessible worksheet within the main workbook (information workbook) and paste the contents right into a newly added sheet inside the similar workbook.
The consolidated information can be accessible within the sheet named Consolidated.
Operating the VBA Code
It’s time to run the newly saved macro code. Copy-paste this code into the VBA editor’s module:
Sub consolidate_shts()
'declare the varied variables used inside the code and the vba information varieties
Dim sht As Worksheet, sht1 As Worksheet, lastrow As Integer, lastrow1 As Integer
'disable display flickering and alert pop-ups through the execution
With Utility
.ScreenUpdating = False
.DisplayAlerts = False
Finish With
'retailer the title of the first workbook within the a macro variable. Change Check.xlsx with the title of your main workbook
Set wbk1 = Workbooks("Check.xlsx")
'activate the workbook earlier than performing the operate(s) on it
wbk1.Activate
'run a vba for loop to test if a sheet Consolidated already exists. If it exists, the for loop will delete it.
For Every sht In wbk1.Sheets
If sht.Title = "Consolidated" Then sht.Delete
Subsequent sht
'Add a brand new sheet to retailer the newly consolidated information
Worksheets.Add.Title = "Consolidated"
'Add some headers to every particular person column inside the consolidated sheet
With Sheets("Consolidated")
.Vary("a1").Worth = "OrderDate"
.Vary("b1").Worth = "Area"
.Vary("c1").Worth = "Rep"
.Vary("d1").Worth = "Merchandise"
.Vary("e1").Worth = "Items"
.Vary("f1").Worth = "UnitCost"
.Vary("g1").Worth = "Complete"Finish With
'The newly created sheet consolidated will maintain the consolidated information from every particular person sheet within the main workbook
For i = 1 To wbk1.Worksheets.Depend
If Sheets(i).Title <> "Consolidated" Then
'Seize the final populated row from the info sheets within the workbook
lastrow = Sheets(i).Vary("a1").Finish(xlDown).Row
'Seize the final populated row within the Consolidated sheet
lastrow1 = wbk1.Sheets("Consolidated").Vary("a1048576").Finish(xlUp).Row + 1
'Copy information from supply sheet and paste it within the consolidated sheet
Sheets(i).Vary("a2:g" & lastrow).Copy Vacation spot:=Sheets("Consolidated").Vary("a" & lastrow1)
Finish If
Subsequent i
'Allow Excel VBA features for future use
With Utility
.ScreenUpdating = True
.DisplayAlerts = True
Finish With
Finish Sub
The VBA Code Defined
First, declare all of the variables you are utilizing inside the code and assign them with the proper VBA information varieties to make the code run seamlessly.
When you declare the variables, some primary housekeeping is required. That is executed by disabling display flickering and suppressing pop-up alerts. For instance, while you delete an present sheet utilizing the VBA code, a immediate inside Excel asks for affirmation earlier than deleting the sheet. Prompts like this are suppressed to boost the pace of execution.
Within the subsequent step, it’s essential outline the workbook’s title, which incorporates all your information. Change Check.xlsx with the title and extension of your workbook title. Be sure to encompass the title with quotes.
Activate the first workbook and delete any present sheets with the title Consolidated to remove any beforehand saved information. The VBA code toggles by every sheet, and as quickly because it encounters the sheet title Consolidated it will delete it. That is executed utilizing the VBA IF assertion, which checks for logical circumstances and deletes the sheet as quickly because the situation is met.
A brand new sheet is added to the first workbook to retailer the consolidated information. Subsequently, pre-formatted, standardized headers are added to this sheet. You possibly can change the values of the titles (column headers) by updating the data subsequent to the cell references inside quotes.
For instance: .Vary(“a1”) = “OrderDate” may be changed with .Vary(“a1”) = “OrderNumber”
Subsequent, a VBA FOR loop toggles by every worksheet, copies the sheet’s contents, and pastes the contents into the Consolidated worksheet earlier than transferring to the following sheet within the workbook. This course of repeats till all sheets are copied over.
Throughout this course of, all of the rows are auto-calculated and pasted within the Consolidated sheet. The final populated row is auto-calculated earlier than the info is pasted in. The macro is dynamic and may regulate to various information rows inside every worksheet.
As soon as information from all sheets is pasted into the principle consolidation sheet, the macro strikes to the ultimate leg of the code. The VBA features initially disabled are enabled once more for future use.
Consolidating A number of Sheets Utilizing Excel VBA Macro
Excel VBA is a superfluous programming language, which works effectively with all Excel parts. Each bit of code is crucial, and it’s necessary to keep in mind that the execution relies on a line-by-line execution system, so that you should not change the order of the code traces.
To customise the code on your necessities, you may make the required adjustments and run this code to consolidate the info effectively and successfully in seconds.
Learn Subsequent
About The Writer
