How to Consolidate data into single sheet from the selected spreadsheets

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.