Try this

[VBA]Sub Automate()
Dim MyArray As Variant
Dim Cel As Range
Dim sh As Worksheet

MyArray = Array("Name2", "Name3", "Name4", "Name5")
With Application
.ScreenUpdating = False: .Calculation = xlCalculationManual: .DisplayAlerts = False
End With

With Sheets("Name1")
Set Cel = .Range("E" & Rows.Count).End(xlUp).Offset(-1, 17)
Range(Cel, .Cells(Cel.Row, Columns.Count)).Resize(2).FillDown
End With

For Each sh In Sheets(MyArray)
Set Cel = sh.Range("B" & Rows.Count).End(xlUp)
Range(Cel, sh.Cells(Cel.Row, Columns.Count)).Resize(2).FillDown
Next
With Application
.DisplayAlerts = True: .Calculation = xlCalculationAutomatic: .ScreenUpdating = True
End With
End Sub
[/VBA]