If you have a specific number of worksheets that have the exact same columns - This macro combines the rows into a new created sheet. The effect is similar to rbind() in R.
Sub Combine()
Dim Acc_Name As Variant
Dim i As Long
Dim j As Integer
Dim newsheet As Worksheet
On Error GoTo Errorcatch
Acc_Name = Array("CLloyds", "NFQ", "SSaver", "CISA", "CLMSaver")
Set newsheet = Worksheets.Add
newsheet.Name = "Combined"
Worksheets(Acc_Name(1)).Range("A1").EntireRow.Copy Destination:=newsheet.Range("A1")
For i = LBound(Acc_Name) To UBound(Acc_Name)
Debug.Print Acc_Name(i)
Worksheets(Acc_Name(i)).Activate
Worksheets(Acc_Name(i)).Range("A1").Select
Selection.CurrentRegion.Select
Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select
Selection.Copy Destination:=newsheet.Range("A65536").End(xlUp)(2)
Debug.Print Worksheets(Acc_Name(i)).Range("A2").Value
Next i
newsheet.Activate
Exit Sub
Errorcatch:
MsgBox Err.Description
End Sub