New sheet Help

[smartguy]

need data in to new sheet based on school.

Excel Workbook
	A	B	C
1	Name	Class	school
2	A	1	kcz
3	b	1	ms
4	c	1	kcz
5	d	1	kcz
6	e	1	kcz
7	f	1	mcz
8	g	1	was
9	h	1	ws
10	i	1	was
11	j	1	was
12	k	1	ss
Sheet1

answer:
Sheet1

Excel Workbook
	A	B	C
1	Name	Class	school
2	A	1	kcz
3	c	1	kcz
4	d	1	kcz
5	e	1	kcz
Sheet1

Sheet2

Excel Workbook
	A	B	C
1	Name	Class	school
2	f	1	mcz
Sheet2

Etc......

 

[MickG]

Hi, Try this:-
NB:- I assumed your workbook already has the appropriate Sheets to copy to. (1 to 7) for the example case.

[COLOR="Navy"]Sub[/COLOR] MG19Jul40
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn1 [COLOR="Navy"]As[/COLOR] Range, c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer,[/COLOR] Dn2 [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Ray, ReRay
n = 1
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("c2"), Range("c" & Rows.Count).End(xlUp))
ReRay = Rng
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn1 [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]If[/COLOR] Dn1 <> "" [COLOR="Navy"]Then[/COLOR]
            c = 0
            ReDim Ray(1 To Rng.Count, 1 To 3)
            c = c + 1
            Ray(c, 1) = Dn1.Offset(, -2): Ray(c, 2) = Dn1.Offset(, -1): Ray(c, 3) = Dn1
        [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn2 [COLOR="Navy"]In[/COLOR] Rng
            [COLOR="Navy"]If[/COLOR] Dn1 = Dn2 And Not Dn1.Address = Dn2.Address [COLOR="Navy"]Then[/COLOR]
                c = c + 1
                Ray(c, 1) = Dn2.Offset(, -2): Ray(c, 2) = Dn2.Offset(, -1): Ray(c, 3) = Dn2
                Dn2 = ""
            [COLOR="Navy"]End[/COLOR] If
        [COLOR="Navy"]Next[/COLOR] Dn2
            n = n + 1
            Sheets("Sheet" & n).Range("A2").Resize(c, 3) = Ray
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Dn1
Rng = ReRay
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[smartguy]

Scrpit out of range

 

[VoG]

Take a look at Excel Explosion (watch the video).

 

[MickG]

Have you got enough Sheets???

 

[smartguy]

No

 

[taigovinda]

Nice link, VoG. Am I failing to understand how to get volume on it, if there is volume?
Also, off topic but: what's an easy way to make a video like that? Thanks!
Tai

 

[VoG]

Hi

You just need to turn up the volume on your PC.

Sorry but I don't know how to make videos like that - I think that you need special software.

 

[smartguy]

Thank you Mr.VoG.

But i need VBA code..

 

[VoG]

See post #12 here http://www.mrexcel.com/forum/showthread.php?t=396069

This is general-purpose VBA that allows you to split the data by any column that you choose. As many new sheets as needed are added. The new sheets can be saved as individual workbooks if you wish. It is also surprisingly fast.