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

 

[mirabeau]

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

1. Automate with macro the copy to another sheet, sorting, and copying back. Can be done very quickly.

2. Post simple example of the type of sorting that's causing you problems.

 

[Heatherlynn]

Thank you for your reply. I have indeed used macros to copy to another sheet, sort, and to copy results back. The issue is that it doesn't seem to be a elegant solution and it will take me another day to finish! Below is a sample of the code used to copy the data.

Dim Ws1 As Worksheet
Dim Ws2 As Worksheet
Set Ws1 = Worksheets("Schedule")
Set Ws2 = Worksheets("Sort")

'Copy from Schedule to sort worksheet
Ws1.Range("C12:E43").Copy
Ws2.Range("C2").PasteSpecial Paste:=xlPasteValues
Ws1.Range("K12:L43").Copy
Ws2.Range("F2").PasteSpecial Paste:=xlPasteValues
Ws1.Range("R12:S43").Copy
Ws2.Range("H2").PasteSpecial Paste:=xlPasteValues
ws1.Range("Y12:z43").Copy
Ws2.Range("J2").PasteSpecial Paste:=xlPasteValues
ws1.Range("AF12:AG43").Copy
.......

This is only 1/20 of the data that needs to be copied, sorted, and copied back.

 

[Sektor]

Is there always 5-columns gap 'tween columns?

 

[Heatherlynn]

Yes. There are formulas in hidden columns.

Heather

 

[Sektor]

Ws1.Range("C12:E43") - Three columns
Subsequent columns are 2 columns.

 

[Heatherlynn]

That is correct.

 

[Sektor]

What's the last column? Does it change?

 

[Sektor]

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
        Ws1.Range(Cells(12, i), Cells(43, i + 1)).Copy
        j = j + 2
        Ws2.Cells(2, j).PasteSpecial xlPasteValues
    Next
    
End Sub

 

[Heatherlynn]

The Last range is a single column. Interesting thought to build a range in this manner. I'll have to play with your code a bit. Thanks.