Function that copy certain raws to another sheet

[katani]

Dear everybody,

I have a master sheet with many columns, names and numbers. one of the columns will read either 0,1,2, or any number above 2 (according to a function).

I need to make 4 new sheets in which the first sheet will copy all rows that reads 0, the second sheet should copy all rows that reads 1, the third sheet will read 2...etc.

Many thanks in advance
Katani

 

[venkat1926]

why not just sort according to THAT column and copy sets

another way is to autofilter according to 0,1,2 etc and copy filtered data to new sheets. . if there are too many numbers FIRST get unique numbers using advance filtering that column data and do autofilter looping through the unique numbers.

 

[Peter_SSs]

Test this in a copy of your workbook.

You'll need to check the name of the 'Master' sheet and the column that contains the numbers 0, 1, 2 etc and adjust the code to suit.

Post back if you need help with how to implement the code and tell us what Excel version you are using.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> SplitMasterData()<br>    <SPAN style="color:#00007F">Dim</SPAN> wsM <SPAN style="color:#00007F">As</SPAN> Worksheet<br>    <SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> Crit <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <br>    <SPAN style="color:#00007F">Const</SPAN> Col <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = "C" <SPAN style="color:#007F00">'<- Column with 0,1,2 etc</SPAN><br>    <SPAN style="color:#00007F">Set</SPAN> wsM = Sheets("Master") <SPAN style="color:#007F00">'<- Change to suit</SPAN><br>    <br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">For</SPAN> i = 0 <SPAN style="color:#00007F">To</SPAN> 3<br>        wsM.Copy After:=Sheets(Sheets.Count)<br>        Crit = IIf(i = 3, "<", "<>") & i<br>        <SPAN style="color:#00007F">With</SPAN> ActiveSheet<br>            .Name = i<br>            <SPAN style="color:#00007F">With</SPAN> Intersect(.UsedRange, .Columns(Col))<br>                .AutoFilter Field:=1, Criteria1:=Crit<br>                .Offset(1).EntireRow.Delete<br>                .AutoFilter<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN> i<br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br></FONT>