martinctc
4/30/2017 - 1:16 PM

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

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.

CombineRowsFromSheets.vbs
content_copyfile_download
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