Forum Discussion
Query related to VBA code
Hello Everyone, How to copy data from Master Data to Individual each sheet.. For example - I want to make sheets for each individual division.. Please help... Here is a attached fi...
Can we do with the help of VBA code ?
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- Sir Can you please explain this code? Please.
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.