If you can be sure that no used cells appear below the data on each sheet,
then

Sub grabRowsIWant()
Dim Sht As Worksheet
Dim SumSht As Worksheet

Set SumSht = Worksheets("SummarySheet")

For Each Sht In ThisWorkbook.Worksheets
If Sht.Name < SumSht.Name Then
Sht.Range("A4:A" & Sht.UsedRange.Rows.Count - 4).EntireRow.Copy
SumSht.Range("A" & SumSht.UsedRange.Rows.Count - 3).Insert
Shift:=xlDown
End If
Next Sht
End Sub



"simora" wrote in message
...
I need to combine (Copy/ Pastelink ) several worksheets into 1 Summary
sheet but I dont need the first 3 rows or last 4 rows of the worksheet to
be copied to the Summary worksheet. except for the first sheet.
The sheets are all formatted alike, but each sheet has varying amounts of
data. All go from A5: to column U except the first sheeet which will start
at A1

I will like to keep the page formatting also. (Shading, colors etc.. )
Headings from the first sheet , Sheet 1 is to be used for Headings of the
summary sheet. It does not have to be created in code.

I dont want the last 4 rows of any sheet posted to the summary sheet.

Sheets 1 - 10 included. Sheet names Sheet 1 - Sheet 10

Any working code samples or help appreciated