vba - Range in Excel to go from a specific cell to the end of the column -

i wondering how define range in order define c5 until last value of column. c5 constant end of list isn't.

sub merge() worksheets("sub").range("c5").end(xltoleft).select.copy worksheets("master worksheet").range("a1:a23").pastespecial paste:=xlpastevalues 

end sub

i getting subscript out of range error. guidance nice.

you don't have copy , paste special, values. transfer values on directly , avoid using clipboard.

this use 1 or 2 variable assignments clean code.

with worksheets("sub")     worksheets("master worksheet").range("a1") _       .resize(.range(.cells(5, 3), .cells(5, columns.count).end(xltoleft)).columns.count, 1) = _       application.transpose(.range(.cells(5, 3), .cells(5, columns.count).end(xltoleft)).value) end 

1. the code little long because need resize destination in order receive values. mentioned, 1 or more vars clean tidily.
2. when creating range(cells, cells.end) type cell range reference, make sure range.cells property inside range object know range .parent property is. in case, i've used with...end statement , prefixed .cells period (aka full stop) know belong worksheets("sub") .
3. use application.transpose flip columns of values rows.