Sub linklinklink()
'
'
'
Dim LastWS As Integer
Dim LastRow As Integer
Dim ShtRow As Integer
Dim MyRange As String
Application.ScreenUpdating = False
'Sheets(1).Select
'Sheets.Add.Name = "Main"
LastRow = Worksheets("Main").Cells(Rows.Count, "A").End(xlUp).Row
LastWS = Worksheets.Count
For WSCount = 4 To LastWS
TotalDwgRef = Sheets(WSCount).Cells(1, Columns.Count).End(xlToLeft).Column
If TotalDwgRef = 27 Then
Sheets(WSCount).Select
Range("A2").Select
ShtRow = Sheets(WSCount).Cells(Rows.Count, "A").End(xlUp).Row - 1 '-1 because of heading
Sheets("Main").Select
Range("A" & LastRow + 1).Select 'New data to be added on the next line after the last row in "Main"
ActiveCell.FormulaR1C1 = "='" & Sheets(WSCount).Name & "'!R[]C[]"
MyRange = ActiveCell.Address & ":" & ActiveCell.Offset(ShtRow, 0).Address
Selection.AutoFill Destination:=Range(MyRange), Type:=xlFillDefault
LastRow = LastRow + ShtRow
EndIf
Next WSCount
Application.ScreenUpdating = True
End Sub