Excel formula or VBS

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("C:\testfile.xlsx")

   'Populate with list of current worksheets
    WS_Count = objExcel.ActiveWorkbook.Worksheets.Count
    For I = 1 To WS_Count
       If objExcel.ActiveWorkbook.Worksheets(I).Name <> "Summary" Then
         WScript.Echo objExcel.ActiveWorkbook.Worksheets(I).Name
       End If
    Next 

Select allOpen in new window

Sub CopyStuff()
Dim i As Long
Dim sht As Worksheet
Dim cell1 As Range, cell2 As Range, rxfer As Range, duprng As Range
For i = 2 To Worksheets.Count
    Set sht = Worksheets(i)
    Set cell1 = IIf(sht.Range("B1") <> "", sht.Range("B1"), sht.Range("B1").End(xlDown))
    Set cell2 = sht.Cells(Rows.Count, 2).End(xlUp)
    Set rxfer = Range(cell1, cell2)
    Sheets("MainSheet").Cells(Rows.Count, 2).End(xlUp).Offset(1).Resize(rxfer.Cells.Count) = rxfer.Value
Next i
With Sheets("MainSheet")
    Set duprng = .Range(.Cells(Rows.Count, 2).End(xlUp).End(xlUp).Offset(1), .Cells(Rows.Count, 2).End(xlUp))
    .Range(duprng.Address).RemoveDuplicates Columns:=1, Header:=xlNo
End With
End Sub

Select allOpen in new window

What do you mean "It will search through all the sheets after sheet 1"? There are no sheets called "Sheet 1", or do you mean the 1st sheet in the book, or do you mean because your example had MainSheet then Sheet2-3-4 and so on?

Sub CopyStuff()
Dim i As Long
Dim sht As Worksheet
Dim cell1 As Range, cell2 As Range, rxfer As Range, duprng As Range
Dim DataCol As Long, DataColMain As Long
Dim HasHeaders As Boolean

'DataCol is the column from which you are pulling data
'Column A = 1, B = 2, etc
DataCol = 2
'DataColMain is the column into which you are dumping data on MainSheet
DataColMain = 2

'Set to true if your data has headers
HasHeaders = False

For i = 2 To Worksheets.Count
    Set sht = Worksheets(i)
    Set cell1 = IIf(sht.Cells(1, DataCol) <> "", sht.Cells(1, DataCol), sht.Cells(1, DataCol).End(xlDown))
    If HasHeaders Then Set cell1 = cell1.Offset(1)
    Set cell2 = sht.Cells(Rows.Count, DataCol).End(xlUp)
    Set rxfer = Range(cell1, cell2)
    Sheets("MainSheet").Cells(Rows.Count, DataColMain).End(xlUp).Offset(1).Resize(rxfer.Cells.Count) = rxfer.Value
Next i
With Sheets("MainSheet")
    Set duprng = .Range(.Cells(Rows.Count, DataColMain).End(xlUp).End(xlUp).Offset(1), .Cells(Rows.Count, DataColMain).End(xlUp))
    .Range(duprng.Address).RemoveDuplicates Columns:=1, Header:=xlNo
End With
End Sub

Select allOpen in new window