sorting macro

Topic

New Reply

I am combining two worksheets into a third and I would like to then sort the data by date/time for each well. Right now, I’m simply copying the 1st worksheet to a new sheet and then copying the 2nd worksheet at the bottom of the new sheet. Attached is an example of the data. Each of the wells will have a different number of rows to sort. So far my attempts at creating a macro haven’t worked. Any suggestions?
Thanks.

Reply | Quote

Replies

JP,

Here is the code to do what you want.

Code:

Public Sub Combine()
Application.ScreenUpdating = False
[COLOR=”#008000″]’——————————-
‘DECLARE AND SET VARIABLES[/COLOR]
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim ws3 As Worksheet
Set ws1 = Worksheets(“Combined”)
Set ws2 = Worksheets(“Wells 1 – 7”)
Set ws3 = Worksheets(“Wells 1 – 7 Off On”)
ws1LastRow = 4
[COLOR=”#008000″]’——————————-
‘CYCLE COLUMNS[/COLOR]
For I = 2 To 20 Step 3
[COLOR=”#008000″]’——————————-
‘COPY SHEET 1[/COLOR]
    ws2LastRow = ws2.Cells(Rows.Count, I).End(xlUp).Row
    For J = 4 To ws2LastRow
        ws1.Cells(J, I) = ws2.Cells(J, I)
        ws1.Cells(J, I + 1) = ws2.Cells(J, I + 1)
    Next J
[COLOR=”#008000″]’——————————-
‘COPY SHEET2[/COLOR]
    ws1NextRow = ws1.Cells(Rows.Count, I).End(xlUp).Row + 1
    ws3LastRow = ws3.Cells(Rows.Count, I).End(xlUp).Row
    For J = 5 To ws3LastRow
        ws1.Cells(ws1NextRow, I) = ws3.Cells(J, I)
        ws1.Cells(ws1NextRow, I + 1) = ws3.Cells(J, I + 1)
        ws1NextRow = ws1NextRow + 1
    Next J
[COLOR=”#008000″]’——————————-
‘SORT COLUMNS[/COLOR]
    ws1LastRow = ws1.Cells(Rows.Count, I).End(xlUp).Row
    With ActiveWorkbook.Worksheets(“Combined”).Sort
        .SortFields.Clear
        .SortFields.Add Key:=Range(Cells(5, I), Cells(ws1LastRow, I))
        .SetRange Range(Cells(4, I), Cells(ws1LastRow, I + 1))
        .Header = xlYes
        .Apply
    End With
Next I
[COLOR=”#008000″]’——————————-
‘CLEANUP[/COLOR]
Set ws1 = Nothing
Set ws2 = Nothing
Set ws3 = Nothing
Application.ScreenUpdating = True
End Sub

Reply | Quote

Thank you so much for your efforts.
Works perfectly and a real time saver.
Thanks.

Reply | Quote