Excel Column Sorting

July 7, 2010 at 21:53:31
Specs: Windows XP
Hi I have a spread sheet which may become huge once and i cannot reveal it.
However i would like to sort the spread sheet based on the coloumns, with a conditional "OK" in some occassions
1. selected 2 coloumns, if data is ok/OK the the entire row has to be copied to another sheet
2. on some occasions selected 3 coloumns has "ok/OK" then entire row has to be copied to another sheet
3. Some occassions if the selected 2 or 3 coloumns have "ok/OK" && AND if the a coloumn "x" is marked as "I" then the entire row has to copied to another sheet.
Please can i get help .. Please want a generic Macro so that i decide which coloumns to look for and what condition.

I created a sample spreadsheet, not sure how to attach it here.

Thanks in advance

See More: Excel Column Sorting

July 10, 2010 at 17:42:54

I have created a macro which will move rows of data based on values in several columns.

The macro contains comments that should allow you to modify it.

The code first tests two columns for OK or ok (you can add extra tests if required within each bracketed OR group). The two column tests are ANDed together so extra columns can be tested by adding further AND operators.

For any row that meets these conditions it is then tested for the value in another column. The value in that other column is used to decide which worksheet to copy the row to. If there is nothing or not a specified value in that column, the row is moved to a default worksheet.

You did not say how this macro is to be used. In this example, all existing data on the destination worksheets is cleared before the data is added. You could remove this initial code if each time the macro was run, the source data was new and needed to be added to existing data.

If new data was added to existing data you would have to include a piece of code to ensure that rows already copied, were not copied again.

The macro assumes that the source data is on a worksheet named "Source"
The default destination is a worksheet names "DestAll"
For this example if column G contains "x" the row is copied to a worksheet named "Dest1" and if column G contains "y" the row is copied to a worksheet named "Dest2".

This logic can easily be extended to other destination worksheets, and the column(G), the values (x and y) and the destination (Dest1 or Dest2) worksheets can easily be changed.

If you have specific issues, please respond with the exact and complete details.

Here is a set of instructions based on Excel 2003. If you are using Excel 2007 or later the procedure is slightly different.

On the Source Worksheet, create a command button from the Control Toolbox toolbar.
(If this isn't visible, right click on an existing toolbar and check the Control Toolbox).
Select the button Icon and draw a button
Right-click the button and select Command Button - Edit and change the name to 'Copy rows to Worksheets' or something suitable.

Right-click the button again and select View Code
In the code window that opens enter this:

Option Explicit

Private Sub CommandButton1_Click()
Dim strThisWs As String
Dim wsEach As Worksheet
Dim rngStart As Range
Dim rngEnd As Range
Dim rngCell As Range
Dim rngDest As Range

On Error GoTo ErrHnd

'clear destination worksheets
'keep the name of the calling sheet
strThisWs = ActiveSheet.Name
'loop through all worksheets in the workbook
For Each wsEach In ActiveWorkbook.Worksheets()
    'only clear the worksheet if it is NOT the calling worksheet
    'or one named "Source" - add other sheet names as required
    If wsEach.Name <> strThisWs And wsEach.Name <> "Source" Then
        'offset one row to leave header row intact
        'this method requires that there is at least one unused row
        'below the data and the bottom of the worksheet
        wsEach.UsedRange.Offset(1, 0).Cells.Clear
    End If
Next wsEach

With Worksheets("Source")
    'set start as A2 i.e., after heading row in column A
    Set rngStart = .Range("A2")
    'set end - last used row in column A
    Set rngEnd = .Range("A" & CStr(Application.Rows.Count)).End(xlUp)
    'loop through cells in column A
    For Each rngCell In .Range(rngStart, rngEnd)
        'test data in various columns
        'This example tests columns C and E (offsets 2 and 4) for
        'OK or ok.
        If (rngCell.Offset(0, 2).Text = "OK" _
                Or rngCell.Offset(0, 2) = "ok") _
                And _
                (rngCell.Offset(0, 4).Text = "OK" _
                Or rngCell.Offset(0, 4) = "ok") Then
            'If both columns have either OK or ok then
            'select where to move the row - in this example based on row G
            'offset 6
            Select Case rngCell.Offset(0, 6).Text
                Case "x"
                'find next empty row on destination sheet named Dest1
                'End (xlup) finds last used row, so offset 1 row
                Set rngDest = Worksheets("Dest1") _
                        .Range("A" & CStr(Application.Rows.Count)) _
                        .End(xlUp).Offset(1, 0)
                'copy & paste entire row
                rngCell.EntireRow.Copy _
                    Destination:=Worksheets("Dest1") _
                Case "y"
                Set rngDest = Worksheets("Dest2") _
                        .Range("A" & CStr(Application.Rows.Count)) _
                        .End(xlUp).Offset(1, 0)
                rngCell.EntireRow.Copy _
                    Destination:=Worksheets("Dest2") _
                Case Else
                'if there is no specific instruction on moving the row to
                'a certain worksheet, just move it to DestAll
                Set rngDest = Worksheets("DestAll") _
                        .Range("A" & CStr(Application.Rows.Count)) _
                        .End(xlUp).Offset(1, 0)
                rngCell.EntireRow.Copy _
                    Destination:=Worksheets("DestAll") _
            End Select
        End If
    Next rngCell
End With
Exit Sub

'error handler
End Sub

Note that Private Sub CommandButton1_Click() and End sub will already be present, so don't duplicate them. Option Explicit goes before Private Sub CommandButton1_Click().
Some lines of code have been split onto two lines for ease of viewing, using the line continuation character "_". This should work 'as is' just copy and paste, or you could remove the "_" and bring the code back to one line.

Click Save from the Visual Basic Menu.

Alt+f11 takes you back to the main Excel window.
Exit design mode (first icon on the Controls Toolbox toolbar).

As changes made by Macros cannot be undone with the Undo button, test this macro on a copy of your data. Always make a backup of your Workbook before running this macro. This code has only been tested on sample data, and it has not been tested in your environment, so test it on copies of your data to ensure that it works 'as expected'

Click the 'Copy rows to Worksheets' button to run the macro


Report •

July 11, 2010 at 02:08:03
Hi Humar,
Thanks a ton .. i will check this tomorrow and let you know how is it working, the first thing i would in office. Please accept my sincere thanks for all your efforts.


Report •

July 12, 2010 at 15:05:11
Hi Humar,
Excellent sir. It works just like that. I modified the commands and column rows and copied all your code under the "copyrows to sheet" a click generated when i modified the caption of the control button. commented out the default name "CommandButton1_Click". created the new sheets as defined and it worked just as i expected it.. Now i am not sure how much i can thank you, hats off to you. Wish i have you as my teacher really ofcourse paid lol.. Thanks a ton Humar.

Regards and Thanks.

Report •

Related Solutions

July 12, 2010 at 16:05:21
Hi freshernz,

Thanks for letting me know it works, and thanks for the kind words.

Best wishes


Report •

Ask Question