Combining specific ranges from multiple worksheets into one

[simora]

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

 

[William Benson]

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

 

[crowleydl]

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

 

[STEVE BELL]

Dawn,

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

 

[crowleydl]

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

 

[STEVE BELL]

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

 

[crowleydl]

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.
(e-mail address removed)

Dawn

 

[STEVE BELL]

Dawn,

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

Patience...