create sheets from filter in a table

[Access Beginner]

Hi,

I have a data set which has has a vew Columns, such as Customer Name, Address, Zone etc. There are 20 columns.

What I am after is to create mutliple sheets by using the Filter on the Column named Zone (there are 16 Zones). So that each Zone has a sheet with the 20 columns list on the main sheet.I am aware that you can do this if you have a Pivot Table, but wasn't sure if Excel had a similar function built in , I've looked but couldn't find it.

If there isn't a similar function, has anyone created a macro or can create one for me please.

I've recorded a Macro but rather than repeat copy and past the 1st part 16 times, is the a more elegant way to achieve the same result?

Sub Zone_Reports()
'
' Zone_Reports Macro
'
'
Sheets("Raw Data").Select
Range("Table2[[#Headers],[Residential Zone]]").Select
ActiveSheet.ListObjects("Table2").Range.AutoFilter Field:=13, Criteria1:= _
"Zone Name 1"
 
Range("Table2[[#Headers],[CRN]]").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets.Add After:=Sheets(Sheets.Count)
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Raw Data").Select
 
ActiveSheet.ListObjects("Table2").Range.AutoFilter Field:=13
Sheets("Raw Data").Select
Range("Table2[[#Headers],[Residential Zone]]").Select
ActiveSheet.ListObjects("Table2").Range.AutoFilter Field:=13, Criteria1:= _
"Zone Name 2"
 
Range("Table2[[#Headers],[CRN]]").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets.Add After:=Sheets(Sheets.Count)
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Raw Data").Select
 
ActiveSheet.ListObjects("Table2").Range.AutoFilter Field:=13
End Sub
[code]
 
Using Excel 2007

 

[AlphaFrog]

Try something like this (not tested).

[color=darkblue]Sub[/color] Zone_Reports()
    [color=green]'[/color]
    [color=green]' Zone_Reports Macro[/color]
    [color=green]'[/color]
    [color=green]'[/color]
    
    [color=darkblue]Dim[/color] x [color=darkblue]As[/color] [color=darkblue]Long[/color]
    
    Application.ScreenUpdating = [color=darkblue]False[/color]
    
    [color=red]For x = 1 To 16[/color] 
    
        Sheets("Raw Data").Select
        Range("Table2[[#Headers],[Residential Zone]]").Select
        ActiveSheet.ListObjects("Table2").Range.AutoFilter Field:=13, Criteria1:= _
                                                           [COLOR="Red"]"Zone Name " & x[/COLOR]
    
        Range("Table2[[#Headers],[CRN]]").Select
        Range(Selection, Selection.End(xlToRight)).Select
        Range(Selection, Selection.End(xlDown)).Select
        Selection.Copy
        Sheets.Add After:=Sheets(Sheets.Count)
        Range("A1").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                                                                        :=False, Transpose:=False
    [color=red]Next x[/color]

    Sheets("Raw Data").Select

    ActiveSheet.ListObjects("Table2").Range.AutoFilter Field:=13
    
    Application.ScreenUpdating = [color=darkblue]True[/color]
    
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

 

[hiker95]

Access Beginner,

See:

DistributeRows
By Norie
http://www.mrexcel.com/forum/showthread.php?t=315083

 

[Access Beginner]

Thanks guys for your replies, they look great.

Cheers