Solved: copy defined rows

[dubber]

hello I'm 100% macro new, plz speak slowly

I have 6 sheets (workbooks): sheet2009, sheet2010, .. sheet2014
in every workbook there is data starting in A4, varies in length

I want in my sheet "summarized" all data, one below the other and the corresponding years (only mentiond in the Name of the Range - last 4 characters)

say in "summarized"

A4 2009 ; B4 (data from sheet 2009 B4)
A5 2009 ; B5 (data from sheet 2009 B5)
a6 2010 (because there is now more data for 2009 in sheet 2009) ; B6 (data from sheet 2010 B4)
...



I hope everything gets clear...

Thanks for any help!

[mdmackillop]

A sample workbook showing the layout of your data would assist.

[dubber]

I just found out its a bit easier then I thought.

All sheets countain the same informations, that means I want to copy six times the same numbers (output in just one column) and every time I want the year next to it.
There are two ways to get the year, it is in the name of the tab and it follows a very easy pattern (2009,2011,..,2014)

[mdmackillop]

[VBA]
Sub Collate()
Dim sh As Worksheet
Dim Rng As Range
For Each sh In Sheets
If Left(sh.Name, 4) = "year" Then
Set Rng = sh.Cells(2, 2).CurrentRegion.Offset(1)
NextCell(4).Offset(, -1).Resize(Rng.Count) = Right(sh.Name, 4)
Rng.Copy NextCell(4)
End If
Next
NextCell(4).Offset(, -1).ClearContents
End Sub

Function NextCell(col As Variant) As Range
Set NextCell = Cells(Rows.Count, col).End(xlUp)(2)
End Function

[/VBA]

[dubber]

Hallo,

thank you very much. This works fine for the example!
However, due to my too simple example it doesn't work 100% right in my big file. the problem ist that there is data everywehere and I just want to copy a certain column.

What can solve the problem?

1. I would like to specify the name of the Output-sheet and the exact starting point. (I guess you did the later, but I can't read it - 100% newby, sorry)

2. I would also like to be very specific on the starting point an column - only the length (number of rows to copy in this column) is a changing

3. Just copy one column - see sample.xls

4. Do not copy format - values only

Thank you for your help - I appreciate this very much!

[mdmackillop]

[VBA]
Sub Collate()
Dim sh As Worksheet
Dim Rng As Range
For Each sh In Sheets
If Left(sh.Name, 4) = "year" Then
Set Rng = sh.Cells.Find("Project-ID")
Set Rng = Range(Rng, sh.Cells(Rows.Count, Rng.Column).End(xlUp)).Offset(1)
NextCell(4).Offset(, -1).Resize(Rng.Count) = Right(sh.Name, 4)
Rng.Copy
NextCell(4).PasteSpecial xlValues
End If
Next
NextCell(4).Offset(, -1).ClearContents
End Sub

Function NextCell(col As Variant) As Range
Set NextCell = Cells(Rows.Count, col).End(xlUp)(2)
End Function
[/VBA]