View Full Version : Solved: copy defined rows
dubber
06-23-2010, 03:00 AM
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
06-23-2010, 05:09 AM
A sample workbook showing the layout of your data would assist.
dubber
06-23-2010, 07:32 AM
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
06-23-2010, 09:23 AM
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
dubber
06-24-2010, 01:58 AM
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
06-24-2010, 11:00 AM
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.