How to consolidate data into single sheet from the selected spreadsheets
Category: [Excel] Demo Available
How to consolidate data into single sheet from the selected spreadsheets
Requirement:
The user has a working document having numerous spreadsheets, from these spreadsheets and needs to extract the data from four spreadsheets having identical structure & consolidate it in another master spreadsheet.
To elaborate, there are below four spreadsheets which are point of interest:
1) BO FY16-17
2) BO FY17-18
3) BO FY18-19
4) BO FY19-20
Each spreadsheet has below header across each column:
1) Customer
2) Sector
3) Country
4) Value in Million
To emphasize again, apart from these spreadsheets there are other spreadsheets in this working document.
The user needs a VBA code to consolidate all the data from these individual spreadsheets to another spreadsheet named 'BO Final' having the same header defined above.
Additional requirements:
1) The data in the rows is dynamic in nature in each of the spreadsheet.
2) Similarly the number of rows will get added in the very near future.
3) In the array, the number of spreadsheets will also get added.
Solution:
This code will work for any number of "BO FY" sheets and any number of rows and columns on the "BO FY" sheets.
Code assigned to the button is
Code:
Sub Update_BO_Final()
Dim x, i As Long, ws As Worksheet
Application.ScreenUpdating = 0
Sheets("BO Final").Cells(1).CurrentRegion.Offset(1).Clear
For Each ws In Sheets
If ws.Name Like "BO FY??-??" Then
With ws.Cells(1).CurrentRegion
x = .Offset(1).Resize(.Rows.Count - 1)
With Sheets("BO Final")
i = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
.Cells(i, 1).Resize(UBound(x, 1), UBound(x, 2)) = x
End With
End With
End If
Next
End Sub
See the following link for the actual files: https://www.ozgrid.com/forum/forum/help-forums/excel-vba-macros/1222094-vba-consolidate-data-into-single-sheet-from-the-selected-spreadsheets
Obtained from the OzGrid Help Forum.
Solution provided by KjBox.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
See also:
| How to scroll worksheets to see last row in selection |
| How to copy information from an excel sheet to a word document |
| How to copy cell value on Sheet 1 to a location in a text in another cell on Sheet 2 |
| How to use VBA code to output multiple worksheets to separate workbooks |
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.