amend code already posted to divide sheets

[rjmdc]

posted by dmt32 a while back
how can i amend this code:
1- to automatically filter column B by heading
2- it crashes when re-run

Sub FilterData()
    'DMT32
    Dim ws1Master As Worksheet, wsNew As Worksheet, wsFilter As Worksheet
    Dim Datarng As Range, FilterRange As Range, objRange As Range
    Dim rowcount As Long
    Dim colcount As Integer, FilterCol As Integer, FilterRow As Long
    Dim SheetName As String, msg As String




    'master sheet
    Set ws1Master = ActiveSheet


    'set the Column you
    'are filtering
top:
    On Error Resume Next
    Set objRange = Application.InputBox("Select Field Name To Filter", "Range Input", , , , , , 8)
    On Error GoTo 0
    If objRange Is Nothing Then
        Exit Sub
    ElseIf objRange.Columns.Count > 1 Then
        GoTo top
    End If


    FilterCol = objRange.Column
    FilterRow = objRange.Row


    With Application
        .ScreenUpdating = False
        .DisplayAlerts = False
    End With


    On Error GoTo progend


    'add filter sheet
    Set wsFilter = Sheets.Add
    
    With ws1Master
        .Activate
        .Unprotect Password:=""  'add password if needed
        
        rowcount = .Cells(.Rows.Count, FilterCol).End(xlUp).Row
        colcount = .Cells(FilterRow, .Columns.Count).End(xlToLeft).Column


        If FilterCol > colcount Then
            Err.Raise 65000, "", "FilterCol Setting Is Outside Data Range.", "", 0
        End If


        Set Datarng = .Range(.Cells(FilterRow, 1), .Cells(rowcount, colcount))
        
        'extract Unique values from FilterCol
        .Range(.Cells(FilterRow, FilterCol), .Cells(rowcount, FilterCol)).AdvancedFilter _
                      Action:=xlFilterCopy, CopyToRange:=wsFilter.Range("A1"), Unique:=True
                      
        rowcount = wsFilter.Cells(wsFilter.Rows.Count, "A").End(xlUp).Row
        
        'set Criteria
        wsFilter.Range("B1").Value = wsFilter.Range("A1").Value


        For Each FilterRange In wsFilter.Range("A2:A" & rowcount)
        
            'check for blank cell in range
            If Len(FilterRange.Value) > 0 Then
            
                'add the FilterRange to criteria
                wsFilter.Range("B2").Value = FilterRange.Value
                'ensure tab name limit not exceeded
                SheetName = Trim(Left(FilterRange.Value, 31))
                
                'check if Filter sheet exists
                On Error Resume Next
                 Set wsNew = Worksheets(SheetName)
                    If wsNew Is Nothing Then
                        'if not, add new sheet
                        Set wsNew = Sheets.Add(after:=Worksheets(Worksheets.Count))
                        wsNew.Name = SheetName
                    Else
                        'clear existing data
                        wsNew.UsedRange.Clear
                    End If
                On Error GoTo progend
                'add / update
                Datarng.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=wsFilter.Range("B1:B2"), _
                                       CopyToRange:=wsNew.Range("A1"), Unique:=False


            End If
            wsNew.UsedRange.Columns.AutoFit
            Set wsNew = Nothing
        Next
        
        .Select
    End With
    


progend:
    wsFilter.Delete
    With Application
        .ScreenUpdating = True
        .DisplayAlerts = True
    End With


    If Err > 0 Then MsgBox (Error(Err)), 16, "Error"
End Sub

 

[rjmdc]

hi
it ran once
then when i ran again it deleted all info but didnt rewrite

 

[rjmdc]

also where in the code does it say data show all?

 

[dmt32]

hi
it ran once
then when i ran again it deleted all info but didnt rewrite

sorry, do not understand what you are referring to - I only suggested where to add a msgbox

Dave

 

[rjmdc]

i understand
i wnt back to original code
after macro is run sheet is blank
where does it say data visible?
also 2nd time macro is run, all worksheets become blank, all data is cleared but none replaced

 

[dmt32]

i understand
i wnt back to original code
after macro is run sheet is blank
where does it say data visible?
also 2nd time macro is run, all worksheets become blank, all data is cleared but none replaced

Without seeing a copy of your complete workbook with some representative sample data I really am not able to understand why you are are having all these issues - maybe another here can offer an alternative approach that would be better suited to your requirement.

Dave

 

[rjmdc]

thank you for everything
much success
i added
ActiveSheet.ShowAllData
so i see my sheet again