Me.Cells.Clear
With Sheets("#1")
.Range(.[A3], .[A3].SpecialCells(xlCellTypeLastCell)).Copy Destination:=Me.Cells(1, 1)
End With
With Sheets("#2")
.Range(.[A4], .[A4].SpecialCells(xlCellTypeLastCell)).Copy Destination:=Me.Cells(Me.Rows.Count, 1).End(xlUp).Offset(1, 0)
End With
With Sheets("#3")
.Range(.[A4], .[A4].SpecialCells(xlCellTypeLastCell)).Copy Destination:=Me.Cells(Me.Rows.Count, 1).End(xlUp).Offset(1, 0)
End With
With Sheets("#4")
.Range(.[A4], .[A4].SpecialCells(xlCellTypeLastCell)).Copy Destination:=Me.Cells(Me.Rows.Count, 1).End(xlUp).Offset(1, 0)
End With
With Me.[A1]
With .CurrentRegion
.Copy
.PasteSpecial Paste:=xlPasteValues
End With
Application.CutCopyMode = False
.Select
End With