Want to augment code to paste only values, would like to change range to specific columns

Asked Mar 13 '18 at 12:41

Active Mar 13 '18 at 12:44

Viewed 21 times

Looking to augment my existing code to paste only values, and would like to change the copy range to select columns only.

Sub CopySort()

    Dim dEnd As Integer

    Sheets("Sorted").Range("A2:R250").ClearContents

    Sheets("Portfolio").Select

        Range("a1").Select

        dEnd = Selection.End(xlDown).Row

        Range("A5:" & "Z" & dEnd).Copy

    Sheets("Sorted").Select

        Range("A2").Select

        ActiveSheet.Paste

        Columns("A:R").Sort key1:=Range("A:A"), order1:=xlAscending, Header:=xlYes, _
                            key2:=Range("F:F"), order2:=xlAscending, Header:=xlYes, _
                            OrderCustom:=1, MatchCase:=False, _
                            Orientation:=xlTopToBottom, DataOption1:=xlSortNormal

    Application.CutCopyMode = False

End Sub

edited Jul 09 '18 at 18:41

Community

asked Mar 13 '18 at 12:41

SirHillaryPushemoff

1

`Dim dEnd As Integer` => `Dim dEnd As Long` -- using Integer risks overflow for no gain. – John Coleman Mar 13 '18 at 12:44
3

What is the question exactly?.. – AntiDrondert Mar 13 '18 at 12:50
2

`Select`, `Paste`, etc. are seldom needed in VBA. See [How to avoid using Select in Excel VBA](https://stackoverflow.com/q/10714251/4996248) – John Coleman Mar 13 '18 at 12:56
What do you mean copy range should select columns only? – BruceWayne Mar 13 '18 at 14:43
Apologies, not as clear as I should have been. Two-part inquiry, first looking to change the paste function to paste values only. Secondly, wondering if I can restrict my copy/paste range to specific columns (ie. A, C, D, H, M etc.) instead of the current range A5:Z – SirHillaryPushemoff Mar 13 '18 at 20:15

Want to augment code to paste only values, would like to change range to specific columns

0 Answers0