Copy all Rows with a cell of value 'x' to a worksheet named 'x'

[sauce1979]

I am a sql developer who has some experience with Excel but none using Macros etc. I export a data set into Excel from SQL Server. For futher analysis I need to split that dataset such that rows with a particular value end up in a particular worksheet with the name of that value.

As an example I have some data as follows:

[B]D_AC        D_RU          D_SBU     f00[/B]
501          207         90001      34
502          208         90021      54
501          208         90001      44
501          208         90312      84
501          209         90001      54
501          209         90211      54
501          210         90021      64
501          210         90001      54

In the example above, I would like to send all rows containing D_RU 207 to a worksheet called 207, _RU 208 to a worksheet called 208, _RU 209 to a worksheet called 209, etc

From what I have read I would need to use a Macro but I am not precisely sure how I would do that. I could easily create this in SQL server but the version I am using at work does not have the functionality needed. Any help you could provide would be very much appreciated

 

[VoG]

Try Excel Explosion http://www.datapigtechnologies.com/freeware.htm

 

[sauce1979]

Hi, I am unable to install the software at work. I was actually hoping for some sort of coding etc

 

[VoG]

OK then try this http://www.mrexcel.com/forum/showthread.php?t=396069

 

[sauce1979]

many thanks. I will give this a go

 

[Yahya]

and this idea for code may be will help you
assume your main sheet name is Data

Sub sauce1979()
Dim LR As Long, c As Range, ws As Worksheet
Set ws = Sheets("DATA")
LR = Range("a" & Rows.Count).End(xlUp).Row
ws.Range("B1:B" & LR).AdvancedFilter xlFilterCopy, , [K1], True
For Each c In ws.Range("k2:k" & Range("K" & Rows.Count).End(xlUp).Row)
    Worksheets.Add(after:=Worksheets(Worksheets.Count)).Name = c.Value
    With ws.Range("a1:D" & LR)
        .AutoFilter 2, c.Value
        .Copy ActiveSheet.Range("a1")
        .AutoFilter
    End With
Next c
ws.Range("k1").EntireColumn.Delete
End Sub