Query related to VBA code

MVP

Jul 19, 2022

Sub SplitData()
    Dim d As Object
    Dim ws As Worksheet
    Dim r As Long
    Dim m As Long
    Dim v As Variant
    Dim wt As Worksheet

    Application.ScreenUpdating = False

    Set d = CreateObject(Class:="Scripting.Dictionary")

    Set ws = Worksheets("Main Sheet")
    m = ws.Range("A" & ws.Rows.Count).End(xlUp).Row
    For r = 2 To m
        d(ws.Range("A" & r).Value) = 1
    Next r

    For Each v In d.Keys
        Set wt = Nothing
        On Error Resume Next
        Set wt = Worksheets(v)
        On Error GoTo 0
        If wt Is Nothing Then
            Set wt = Worksheets.Add(After:=Worksheets(Worksheets.Count))
            wt.Name = v
        Else
            wt.UsedRange.Clear
        End If
        ws.UsedRange.AutoFilter Field:=1, Criteria1:=v
        ws.UsedRange.Copy Destination:=wt.Range("A1")
        wt.UsedRange.EntireColumn.AutoFit
    Next v

    ws.UsedRange.AutoFilter

    Application.ScreenUpdating = True
End Sub

Excel
Iron Contributor
Jul 19, 2022
Sir Can you please explain this code? Please.
- HansVogelaar
  MVP
  Jul 19, 2022
  Excel
  
  The code first creates a Dictionary object
  
  It then loops through the Division names in column A of the Main Sheet, and creates a new item in the dictionary for each unique division. So at the end of that loop, the dictionary has 4 items: North, East, South and West.
  
  Next, the code loops through these dictionary item. For each of them, it checks if there is already a worksheet with that name. If not, it creates a new sheet and names it, otherwise it clears the existing sheet.
  
  The Main Sheet is filtered on the item (division), and the filtered range is copied to the sheet named after the division.
  
  At the end, the filter on the Main Sheet is turned off.

Forum Discussion