CODEWITHSUNDEEP
excelvba
Wilz
Wilz

Reputation: 239

Sort a Range before pasting Excel VBA

I'm using the following code to copy a particular range from a Workbook to another Workbook, its working fine.

But now i need to sort the Range in ascending order just before pasting to the destination sheet without changing the source. Please help.

With Workbooks(strExcelFile).Sheets(strSheetName)
     .Range(strRange).Copy
End With

ActiveSheet.Range(strDestCell).PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False

Upvotes: 1

Views: 7503

Answers (2)

brettdj
brettdj

Reputation: 55682

You should try to avoid the redundant Select when working with ranges. You can work more cleanly using worksheets and ranges as below, which is easily adaptable accross workbooks as per your question

code

Sub ReCut()
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Dim rng1 As Range
    Set ws1 = ThisWorkbook.Sheets(1)
    Set ws2 = ThisWorkbook.Sheets(2)
    Set rng1 = ws1.Range("A1:A10")
    With ws2.[b1].Resize(rng1.Rows.Count, 1)
        .Value = rng1.Value
        .Sort ws2.[b1]
    End With
End Sub

Upvotes: 1

Joe
Joe

Reputation: 63424

Take advantage of the fact that once you paste, your newly pasted range will be selected; then you can use SELECTION.

Public Sub test()
     Range("A1:A8").Copy
    ActiveSheet.Range("B1").PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False
    Selection.Sort key1:=Range("B1")
End Sub

That test example will work in any excel file with some data in A1-A8. B1 in both places can be replaced with strDestCell and A1:A8 with strRange for your eventual subroutine.

Upvotes: 2

Related Questions