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

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

Set SumSht = Worksheets("Corp Sum")

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

above is the code, and I get an error at "Shift:=xlDown". I am no
sure that the code will do what I want, but I have been trying al
possible solutions that I find posted here at the forum.

I have a vlookups array on 30 worksheets that is pulling specifi
information about sponsors into the same setup (range J3-M16) on eac
worksheet. the sponsors are in a list of mixed items at a differen
part of each worksheet (columns A-E with headers in row 5). this lis
will be manually added to throughout the year. to keep from doin
double work, I am trying to list all of the sponsor information fro
each sheet on a separate sheet automatically (this will also preven
mistakes). I want to be able to use autofilter on the sponsor sheet
to sort by date, amount, member, alpha, etc., as the need arises.
would be happy to provide you with a copy of the file.


Dawn




STEVE BELL Wrote:
Dawn,

Not a problem!

But I'll need to see your code and a brief explanation of how you wan
it to
behave.

Make sure I understand what is supposed to happen.

It helps to use the Reply Group function so that the previous post(s
is
included - this makes it easier to see what has been said and tried...

So reply back with a copy of your code...

--
steveB

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

Thank you for the response, but I am new to macros and do not kno
how
to do it manually. If I understand what it is doing, I can adapt a
macro once I have copied it, but I cannot create one.

Dawn


--
crowleydl

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


--
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,

Haven't forgotten you... Have to go out and will be back in a few hours.

Patience...

--
steveB

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

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

Set SumSht = Worksheets("Corp Sum")

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

above is the code, and I get an error at "Shift:=xlDown". I am not
sure that the code will do what I want, but I have been trying all
possible solutions that I find posted here at the forum.

I have a vlookups array on 30 worksheets that is pulling specific
information about sponsors into the same setup (range J3-M16) on each
worksheet. the sponsors are in a list of mixed items at a different
part of each worksheet (columns A-E with headers in row 5). this list
will be manually added to throughout the year. to keep from doing
double work, I am trying to list all of the sponsor information from
each sheet on a separate sheet automatically (this will also prevent
mistakes). I want to be able to use autofilter on the sponsor sheet,
to sort by date, amount, member, alpha, etc., as the need arises. I
would be happy to provide you with a copy of the file.


Dawn




STEVE BELL Wrote:
Dawn,

Not a problem!

But I'll need to see your code and a brief explanation of how you want
it to
behave.

Make sure I understand what is supposed to happen.

It helps to use the Reply Group function so that the previous post(s)
is
included - this makes it easier to see what has been said and tried...

So reply back with a copy of your code...

--
steveB

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

Thank you for the response, but I am new to macros and do not know
how
to do it manually. If I understand what it is doing, I can adapt a
macro once I have copied it, but I cannot create one.

Dawn


--
crowleydl

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



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