Results 1 to 3 of 3

Thread: sorting macro

  1. #1
    4 Star Lounger
    Join Date
    May 2002
    Posts
    410
    Thanks
    1
    Thanked 0 Times in 0 Posts

    sorting macro

    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.
    Attached Files Attached Files

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 651 Times in 593 Posts
    JP,

    Here is the code to do what you want.

    Code:
    Public Sub Combine()
    Application.ScreenUpdating = False
    '-------------------------------
    'DECLARE AND SET VARIABLES
    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
    '-------------------------------
    'CYCLE COLUMNS
    For I = 2 To 20 Step 3
    '-------------------------------
    'COPY SHEET 1
        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
    '-------------------------------
    'COPY SHEET2
        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
    '-------------------------------
    'SORT COLUMNS
        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
    '-------------------------------
    'CLEANUP
    Set ws1 = Nothing
    Set ws2 = Nothing
    Set ws3 = Nothing
    Application.ScreenUpdating = True
    End Sub
    Attached Files Attached Files
    Last edited by Maudibe; 2014-12-04 at 00:15. Reason: added sort code

  3. #3
    4 Star Lounger
    Join Date
    May 2002
    Posts
    410
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thank you so much for your efforts.
    Works perfectly and a real time saver.
    Thanks.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •