How to copy required rows of data from one excel sheet to another based on a condition?

Question

hi, I have an excel sheet of some 7k rows of data, I need to segregate that data into three different sheets based on a column( group name).

So , I need to segregate the data based on the group name (there are 3 groups) into three different sheets.

Please let me know regarding this...

Thanks in advance...

Kar

Answer 1

You can try something like this, put it in a macro and run on demand

Sub Macro1()
Dim rowCounter As Integer
Dim usedRange As Range
Dim sourceSheet As Worksheet, dest1 As Worksheet, dest2 As Worksheet, dest3 As Worksheet
Dim dest1Row As Integer, dest2Row As Integer, dest3Row As Integer

    dest1Row = 1
    dest2Row = 1
    dest3Row = 1
    Set sourceSheet = Sheets("Sheet1")
    Set dest1 = Sheets("Sheet2")
    Set dest2 = Sheets("Sheet3")
    Set dest3 = Sheets("Sheet4")
    Set usedRange = sourceSheet.usedRange

    For rowCounter = 1 To usedRange.Rows.Count
        If (usedRange(rowCounter, 1) = 1) Then
            usedRange.Range(Cells(rowCounter, 1), Cells(rowCounter, usedRange.Columns.Count)).Copy dest1.Range("A" & dest1Row)
            dest1Row = dest1Row + 1
        ElseIf (usedRange(rowCounter, 1) = 2) Then
            usedRange.Range(Cells(rowCounter, 1), Cells(rowCounter, usedRange.Columns.Count)).Copy dest2.Range("A" & dest2Row)
            dest2Row = dest2Row + 1
        ElseIf (usedRange(rowCounter, 1) = 3) Then
            usedRange.Range(Cells(rowCounter, 1), Cells(rowCounter, usedRange.Columns.Count)).Copy dest3.Range("A" & dest3Row)
            dest3Row = dest3Row + 1
        End If
    Next rowCounter
End Sub