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

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

I am trying to copy a specific range from 30 worksheets onto a summar
sheet, where I can sort the data. I thought this solution might hel
(even though I want the summary to be dynamic, but when I tried usin
the code, and I get a syntax error at "Shift:=xlDown".

Thanks,
Daw

--
crowleyd
-----------------------------------------------------------------------
crowleydl's Profile: http://www.excelforum.com/member.php...fo&userid=2596
View this thread: http://www.excelforum.com/showthread.php?threadid=37511

Dawn,

Record the steps while doing this manually. The recorded code should show
you the syntax you need.

--
steveB

Remove "AYN" from email to respond
"crowleydl" wrote
in message ...

I am trying to copy a specific range from 30 worksheets onto a summary
sheet, where I can sort the data. I thought this solution might help
(even though I want the summary to be dynamic, but when I tried using
the code, and I get a syntax error at "Shift:=xlDown".

Thanks,
Dawn


--
crowleydl
------------------------------------------------------------------------
crowleydl's Profile:
http://www.excelforum.com/member.php...o&userid=25968
View this thread: http://www.excelforum.com/showthread...hreadid=375119