Copy column data from multple sheets and merge into another sheet

[connieb]

Hi. I have three sheets in my workbook that I want to copy a range of data from one specific column (different in each sheet) and put together in a column in another sheet in the same workbook.

Is there an easy way to do this?

I tried recording a macro and modifying, but keep getting errors...

Thanks in advance

[humdingaling]

as you cannot just copy the whole column 3 times (as it will copy all lines including blank), the problem you have is finding the last row of the column
record macro will never tell you this
here is some reading on this topic
http://www.rondebruin.nl/win/s9/win005.htm

i think once you know how to find the last row you would know what to do?

[JOHN H. DAVIS]

Maybe:

Please Login or Register  to view this content.

Would load the consolidation into Column("A")

[connieb]

Thanks John, that was just what I was after, however, I didn't state that I would like the data pasted into "Another Sheet" from cell N10 down and any blanks removed. I've given it a go myself, but I think I am just wasting my time. How would I add those conditions into the code you provided? Thanks in advance.

[JOHN H. DAVIS]

Maybe:

Please Login or Register  to view this content.

[connieb]

Thanks again, I have amended the data to match what I am using, but when I run the macro nothing happens. I have attached the amended code, can you see anything that may be causing this?

I am trying to copy from the sheets named "BPA - Create ü", "SPO - Create ü". The data is I want starts at N12. The copy destination is in the sheet named "PO Close" from cell F12 down.

Cheers
Connie

Sub GetRecords_Click()
Dim x As Range, ws As Worksheet, y As Long, z As Long
z = Sheets("PO Close").Range("F" & Rows.Count).End(3).Row
For Each ws In ActiveWorkbook.Worksheets
Select Case ws.Name
Case Is = "BPA - Create ü", "SPO - Create ü"
Set x = ws.Rows(9).Find("PO Number", LookIn:=xlValues, Lookat:=xlWhole)
If Not x Is Nothing Then
y = ws.Cells(Rows.Count, x.column).End(3).Row
Select Case z
Case Is = 10
ws.Range(Cells(2, x.column), Cells(y, x.column)).Copy Sheets("PO Close").Range("F10")
Case Is > 10
ws.Range(Cells(2, x.column), Cells(y, x.column)).Copy Sheets("PO Close").Range("F" & Rows.Count).End(3)(2)
End Select
End If
Set x = Nothing
End Select
Next ws
Sheets("PO Close").Range("F10:F" & Range("F" & Rows.Count).End(3).Row).SpecialCells(4).Delete xlUp
End Sub

[JOHN H. DAVIS]

Connie, please use code tags when posting too the Forum. See Forum Rule No. 3.

Please Login or Register  to view this content.

Currently your find row is 9.

Please Login or Register  to view this content.

When you say " The data is I want starts at N12." Please explain further. You don't want the same Column that "PO Number" is in?

[connieb]

Thanks, "PO Number" is in column N, the first cells with a value is in row 12, column N. I have headers, but they don't start until row nine, there are a few more rows that have different formatting inbetween which I don't want to include. The sheet PO Close has the heading PO Number also, so I don't need that to be included in the copy, just the data. The column that PO Header is in 'PO Close' is column F, and I trying to get it to paste from row 12 downward in that column. Thanks.