Splitting Data into Specific Worksheets

[itsianusa]

Hi all,

I'm trying to make a macro that will be able to split a worksheet into three by one columns information that has numbers 1-3 in them. I want all the rows that have a "1" in this specific column to go to one worksheet, all rows with a "2" to go into the next, etc...

Does anyone know how to populate these worksheets with a macro? I'm thinking a For/Next loop, but I have to admit that I'm quite rusty with even simple VB...

Thanks!

 

[Joe Was]

You may be able to modify this to your needs?

Sub myFindXSh1to2()
'Run from standard module, like: Module1.
Dim c As Range, rng As Range, myCount
Dim Message, Title, Default, myFValue

'Get item to search for!
Message = "Find and move the rows," & vbCr & _
"that contain the string you enter below!" & vbCr & vbCr & _
"Only look in columns ""A"" through ""C.""" & vbCr & vbCr & _
"Search Sheet1, if found, move data" & vbCr & _
"to sheet2!" ' Set prompt.
Title = "Search Columns A through C!" ' Set title.
Default = "Test" ' Set default.
' Display message, title, and default value.
myFValue = InputBox(Message, Title, Default)

Application.ScreenUpdating = False
'Search range!
Set rng = Sheets("sheet1").Range("A2", Range("C65536").End(xlUp))
For Each c In rng
If c.Value = myFValue Then
myCount = myCount + 1
c.EntireRow.Copy Sheets("Sheet2").Range("A65536").End(xlUp).Offset(1, 0)
c.Rows.EntireRow.Delete
End If
Next c
Application.ScreenUpdating = True

MsgBox "Found and moved: " & myCount & ", rows of data!"
End Sub