Here is a version of your code that copies data from D9 to the end of the data used and pastes the results into the [Comprehensive] sheet starting at cell D9 instead of A2. It copies the data present from D9 to the last column rather than attempting to copy all of the columns for the rows used otherwise there would be not enough columns for the copy to take place.
I'll leave it to you to spot the differences
Option Explicit
Sub CopyDataWithoutHeaders()
Dim sh As Worksheet
Dim DestSh As Worksheet
Dim Last As Long
Dim shLast As Long
Dim CopyRng As Range
Dim StartRow As Long
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
Application.DisplayAlerts = False
On Error Resume Next
ActiveWorkbook.Worksheets("Comprehensive").Delete
On Error GoTo 0
Application.DisplayAlerts = True
Set DestSh = ActiveWorkbook.Worksheets.Add
DestSh.Name = "Comprehensive"
StartRow = 9
For Each sh In ActiveWorkbook.Worksheets
If sh.Name <> DestSh.Name Then
Last = LastRow(DestSh)
shLast = LastRow(sh)
If Last < 9 Then Last = 8
Dim lastCell As String
lastCell = Replace(Cells(1, sh.UsedRange.Columns.Count).Address(False, False), "1", CStr(shLast))
If shLast > 0 And shLast >= StartRow Then
Set CopyRng = sh.Range("D" & CStr(StartRow) & ":" & lastCell)
If Last + CopyRng.Rows.Count > DestSh.Rows.Count Then
MsgBox "There are not enough rows in the " & _
"summary worksheet to place the data."
GoTo ExitTheSub
End If
CopyRng.Copy
With DestSh.Cells(Last + 1, "D")
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
Application.CutCopyMode = False
End With
End If
End If
Next
ExitTheSub:
Application.Goto DestSh.Cells(1)
DestSh.Columns.AutoFit
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub
Private Function LastRow(ws As Worksheet) As Long
LastRow = ws.UsedRange.Rows.Count
End Function