Copy and paste macro onto single sheet

Hi there,
Does anyone have a macro that can:

1. Copy a matrix (B2:H50), paste it onto summary sheet
2. Go back to the sheet and copy (T15:X30) then paste that underneath the first paste on the summary sheet.
3. Copy from all sheets that have e.g "x" in A1 (or something that indicates that it is a sheet that is to be copied from).
4. When finished, on the summary sheet remove all the spare rows that have nothing in them.

If someone has somethign like this I may be able to modify it...perhaps!!

Any help appreciated as always

Cheers

Simon

Thanks Martin, you're good sort!
I will try it now and report back

Cheers

Simon

Answer to 1
Sheets("Matrix sheet name").Range("B2:H50").Select
Selection.Copy
Sheets("Summary").Activate
Sheets("Summary").Range("B2").Select
Selection.Paste

Answer to 2
Sheets("Matrix sheet name").Range("T15:X30").Select
Selection.Copy
Sheets("Summary").Activate
Sheets("Summary").Range("B51").Select ‘to put this data under the range pasted in answer 1Selection.Paste

Answer to 3
For i = ActiveWorkbook.Sheets.Count To 1 Step -1

If ActiveWorkbook.Sheets(i).Range("a1") = "x" Then
‘INSERT YOUR CODE HERE TO DO COPYING IF CELL A1 CONTAINS AN x End If
Next

Answer to 4
Visit this link, the pearson site will have the required routine you need for this which you can modify to suit your file.
http://www.cpearson.com/excel/deleting.htm#DeleteBlankRows

You will need to put the correct sheet names into the code above & it may also be a good idea to do the pasting on the summary sheet by "finding the last row used" rather than paste into a specific cell. using something like----
LastRow = Cells(Cells.Rows.Count,"A").End(xlUp).Row
Hope this helps

Hey There Guys,
this macro works great!!! Thanks both of you for your input!
I forgot to mention, I need it to copy paste values as there are some formulas I need to get rid of. I tried adding some code after the copy line for a paste special, values but it does not work
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False

Any ideas

Thanks

Simon

put this in your code, i think it works for values only rather than formula's etc

Sheets("TARGET sheet").Range("A10:A159") = Sheets("SOURCE sheet").Range("D10:D159").Value

obviously you need to change the target sheet and source sheet and ranges to suit

hope this works

Thanks Peejay,
I need to run with Martin's macro as it is not sheet name sensitive. The users of this workbook will add new worksheets and rename them as they wish. I need to copy the "values" from these worksheets and paste into the summary worksheet.
So it looks something like this so far (I have mucked with it because I need to copy several areas in each sheet)
Sub comptgen()
For Each Sheet In ThisWorkbook.Sheets
If Sheet.Name <> "Summary" And Sheet.Cells(5, 1) = "Octet no." Then
Sheet.Range("A2").Copy Destination:=Sheets("Summary").Cells(65536, 1).End(xlUp).Offset(1, 0)
Sheet.Range("b6:i46").Copy Destination:=Sheets("Summary").Cells(65536, 1).End(xlUp).Offset(1, 0)
Sheet.Range("l1:q15").Copy Destination:=Sheets("Summary").Cells(65536, 1).End(xlUp).Offset(1, 0)
Sheet.Range("AA6").Copy Destination:=Sheets("Summary").Cells(65536, 1).End(xlUp).Offset(1, 0)

End If
Next Sheet
End Sub



Cheers


Simon