Thread: copy column to new sheet

I have a very simple task: I have a column listing a bunch of names on a sheet called "Names", and I want to include in a subroutine the code to copy those names into the last sheet starting at cell A2 and going down. When I write the code as 3 separate lines, it works just fine:

Code:

Worksheets("Names").Range("A1").Select
Worksheets("Names").Range("A1", Selection.End(xlDown)).Select
Selection.Copy Destination:=Worksheets(Sheets.Count).Range("A2")

But when I try to put it all together as one line, it chokes:

Code:

Worksheets("Names").Range("A1", Selection.End(xlDown)).Copy Destination:=Worksheets(Sheets.Count).Range("A2")

Now, I know that since I have the code working in 2 lines, I should just be happy an move on, but the perfectionist in me wants to know what about the one-line version I'm screwing up. Any suggestions?

Thanks,
Adam

hi Adam,
I like your need for perfection - try this one out, it doesn't cut it down to 1 line, but is tidy & links the ranges to the worksheet* using the With statement.
*rather than to the "selection" - in the search for perfection, "select" or "selection" often causes choking!

Code:

    With ThisWorkbook.Worksheets("Names")
        .Range("A1", .Cells(1, 1).End(xlDown)).Copy Destination:=ThisWorkbook.Worksheets(Sheets.Count).Range("A2")
    End With

Edit: Explanation, by removing the ...".select" of the first line, you are removing certainty of what the selection actually is, but certainty is needed with this code because of the use of "selection" in the second line of your original code.

hth
Rob

Originally Posted by broro183

hi Adam,
I like your need for perfection - try this one out, it doesn't cut it down to 1 line, but is tidy & links the ranges to the worksheet* using the With statement.
*rather than to the "selection" - in the search for perfection, "select" or "selection" often causes choking!

Code:

    With ThisWorkbook.Worksheets("Names")
        .Range("A1", .Cells(1, 1).End(xlDown)).Copy Destination:=ThisWorkbook.Worksheets(Sheets.Count).Range("A2")
    End With

Edit: Explanation, by removing the ...".select" of the first line, you are removing certainty of what the selection actually is, but certainty is needed with this code because of the use of "selection" in the second line of your original code.

hth
Rob

Glorious! Thanks for the help, Rob.

I'm pleased I could help - thanks for the feedback & for adding to my reputation

Rob