Sorting problem with noncontigous range

[Heatherlynn]

I have developed a rather sophisticated workbook that will be used to schedule nurses. Although I have found a "work around" I thought I'd ask for suggestions in the forum. I have colums of data that are separated by several columns. When recording a macro to sort the data I can't use noncontigous ranges. The only solution I could find was to copy the data to another sheet, sort it, and then copy back. I found this to be a very tedious process. Any suggestions? Thanks.

Heather

 

[Sektor]

It's no wonder to construct Range object this way. When you type Range( you see two arguments: Cell1 and [Cell2]. The Cell2 is optional, but I used it. Arguments can be strings or Range objects.
Such construction helps to create very flexible code.

I just note one thing in my code. If you run from the sheet other than Schedule or Sort, you'll get incorrect ranges. Here's correct way.

Sub Heatherlynn()

    Dim i As Long, j As Long
    Dim Ws1 As Worksheet, Ws2 As Worksheet
    
    Set Ws1 = Worksheets("Schedule")
    Set Ws2 = Worksheets("Sort")

    j = 4
    
    ' Special case with three columns.
    Ws1.Range("K12:L43").Copy
    Ws2.Range("F2").PasteSpecial xlPasteValues

    ' Change last column (first column of two). My last column is 500.
    For i = 11 To 500 Step 7
[B][COLOR="Blue"]        With Ws1
            .Range(.Cells(12, i), .Cells(43, i + 1)).Copy
        End With[/COLOR][/B]
        j = j + 2
        Ws2.Cells(2, j).PasteSpecial xlPasteValues
    Next
    
End Sub

 

[Heatherlynn]

thanks for your help. Do you know if you can copy locked cells and sort those as well?