VBA - Move rows when value of one cell falls into 1 of 4 ranges

[m_gobo]

Hi Experts,
I would like a macro that splits the following data into 4 separate sheets. The rows should be divided based on the value of Column G. Each of the 4 sheets should contain a range of values of Column G; the ranges are: 0-89, 90-179, 180-269, 270 & up.

This seems rather simple but I am new to VBA. Any and all help is appreciated!

 

[mikenelena]

Hi m_gobo,

I have a code for you. I tested it out with a very limited amount of data, and it seems to do exactly what you require. Just rename the worksheets in the code to whatever you need. If this solves your issue, please remember to mark the post "Solved". Good luck, and let us know if you have any issues.

Sub SplitDataByColumnG2()

Dim ws As Worksheet
Dim lastRow As Long
Dim rng As Range
Dim cellValue As Long

Set ws = Worksheets("Master List")

For Each cell In ws.Range("G2:G" & ws.Cells(Rows.Count, "G").End(xlUp).Row)
    If IsNumeric(cell.Value) Then
        cellValue = cell.Value
        Select Case cellValue
            Case 0 To 89
                cell.EntireRow.Copy Destination:=Worksheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
            Case 90 To 179
                cell.EntireRow.Copy Destination:=Worksheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
            Case 180 To 269
                cell.EntireRow.Copy Destination:=Worksheets("Sheet3").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
            Case Is >= 270
                cell.EntireRow.Copy Destination:=Worksheets("Sheet4").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
        End Select
    End If
Next cell

End Sub