create sheets from filter in a table

Access Beginner

Active Member
Joined
Nov 8, 2010
Messages
311
Office Version
  1. 2016
Platform
  1. Windows
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?

Code:
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
 
Last edited:

Some videos you may like

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,398
Try something like this (not tested).

Code:
[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]
 

Watch MrExcel Video

Forum statistics

Threads
1,126,888
Messages
5,621,432
Members
415,839
Latest member
Pollydooner

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top