• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Create a New Sheet for Each Item in an AutoFilter

Hello.

Wanted to create a new sheet with specific data from autofilter as of now am trying to autofilter and copy visible cells and pasting in new sheet and renaming it.

But the problem is data is keep increasing on monthly basis..Please advise.
 
Hi !

Yes, create !

Range.Copy method (like Delete) takes only visible cells
so it is very not a concern …
No need to paste as you can read within Range.Copy VBA inner help !

No matter if data increase or not, why it could be a problem ?

Just start with Macro recorder, you will get a free code base !
Come back with a precise technical question …
 
Try this!

Code:
Sub Create_New_Sheet()
'Step 1: Declare your Variables
  Dim MySheet As Worksheet
    Dim MyRange As Range
    Dim UList As Collection
    Dim UListValue As Variant
    Dim i As Long
   
'Step 2:  Set the Sheet that contains the AutoFilter
    Set MySheet = ActiveSheet
   
   
'Step 3: If the sheet is not auto-filtered, then exit
    If MySheet.AutoFilterMode = False Then
        Exit Sub
    End If
   
 
'Step 4: Specify the Column # that holds the data you want filtered
    Set MyRange = Range(MySheet.AutoFilter.Range.Columns(1).Address)
   

'Step 5: Create a new Collection Object
    Set UList = New Collection
   

'Step 6:  Fill the Collection Object with Unique Values
    On Error Resume Next
    For i = 2 To MyRange.Rows.Count
    UList.Add MyRange.Cells(i, 1), CStr(MyRange.Cells(i, 1))
    Next i
    On Error GoTo 0
   

'Step 7: Start looping in through the collection Values
    For Each UListValue In UList
 
 
'Step 8: Delete any Sheets that may have bee previously created
        On Error Resume Next
        Application.DisplayAlerts = False
        Sheets(CStr(UListValue)).Delete
        Application.DisplayAlerts = True
        On Error GoTo 0
   
   
'Step 9:  Filter the Autofilter to macth the current Value
        MyRange.AutoFilter Field:=1, Criteria1:=UListValue
   
   
'Step 10: Copy the AutoFiltered Range to new Workbook
        MySheet.AutoFilter.Range.Copy
        Worksheets.Add.Paste
        ActiveSheet.Name = Left(UListValue, 30)
        Cells.EntireColumn.AutoFit
       

'Step 11: Loop back to get the next collection Value
    Next UListValue


'Step 12: Go back to main Sheet and removed filters
    MySheet.AutoFilter.ShowAllData
    MySheet.Select
 
  End Sub
 
Back
Top