Query related to VBA code

Question

Hello Everyone,&nbsp;&nbsp;How to copy data from Master Data to Individual each sheet..&nbsp;For example - I want to make sheets for each individual division..&nbsp;&nbsp;Please help...&nbsp;&nbsp;Here is a attached file...

jkpieterse · Answer

Excel&nbsp;Convert your data to a table and insert a slicer on the Division column, no extra worksheets needed.

excel · Answer

Can we do with the help of VBA code ?

hansvogelaar · Answer

Excel&nbsp;
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" &amp; ws.Rows.Count).End(xlUp).Row
    For r = 2 To m
        d(ws.Range("A" &amp; 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 · Answer

Sir Can you please explain this code? Please.

hansvogelaar · Answer

Excel&nbsp;
The code first creates a Dictionary object&nbsp;
&nbsp;
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.
&nbsp;
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.
&nbsp;
At the end, the filter on the Main Sheet is turned off.

Forum Discussion

Query related to VBA code

5 Replies