Split Master Data In Excel

How to split MASTER DATA Into multiple sheets using VBA Macro.


We need to write code as per the steps which we follow in manual work. In this automation, I have written code step by step.

  • Declare the variables as needed
  • We have required to use an advanced filter or we can use remove duplicates also. After using this we can know, how many total unique values are available.
  • As per the total unique values, Loop will be running.
  • We will add a worksheet and will rename the worksheet.
  • Use an auto filter in data and select one by one value.
  • Copy all the data and paste it into a newly added sheet.
  • After completing all processes we will save the workbook.


Sub Split_Data()
    Dim iRow As Integer
    Dim Rng As Range
    Dim LoopCounter As Integer
    Dim ws As Worksheet
   
    iRow = Sheet1.Cells(Rows.Count, ""B"").End(xlUp).Row
   
    'Set keyword is used for creating an object or assigning a reference to an object
   
    Sheet1.Range(""M1"").CurrentRegion.ClearContents
    Set Rng = Sheet1.Range(""B1:B"" & iRow)
    Rng.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Sheet1.Range(""M1""), Unique:=True
   
    For LoopCounter = 2 To Sheet1.Cells(Rows.Count, ""M"").End(xlUp).Row
       
        Sheet1.Range(""A1:E1"").AutoFilter Field:=2, Criteria1:=Sheet1.Range(""M"" & LoopCounter).Value
       
        Set ws = Sheets.Add(After:=Sheets(Sheets.Count))
        ws.Name = Sheet1.Range(""M"" & LoopCounter).Value
       
        Sheet1.Range(""A1"").CurrentRegion.SpecialCells(xlCellTypeVisible).Copy
        ws.Range(""A1"").PasteSpecial xlPasteAll
       
        Application.CutCopyMode = False
       
    Next LoopCounter
    Sheet1.AutoFilterMode = False
   
End Sub

 



How to split MASTER DATA Into multiple Workbooks using VBA Macro.

  • Declare the variables as needed
  • We have required to use an advanced filter or we can use remove duplicates also. After using this we can know, how many total unique values are available.
  • As per the total unique values, Loop will be running.
  • We will add a workbook
  • Use an auto filter in data and select one by one value.
  • Copy all the data and paste it into a newly added workbook.
  • After completing all processes we will save the workbook.


Sub SplitMaster()
    
    Dim Wkb As Workbook
    Dim Wsh As Worksheet
    Dim CityRng As Range
    Dim AgentRng As Range
    Dim DataCount As Integer
    Dim CityCount As Integer
    Dim AgentCount As Integer
    Dim iRow As Integer
    
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Sheet1.AutoFilterMode = False
    
    DataCount = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row
    Set CityRng = Sheet1.Range("B1:B" & DataCount)
    
    Sheet2.Range("K1").CurrentRegion.ClearContents
    CityRng.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Sheet2.Range("K1"), Unique:=True
    
    For CityCount = 2 To Sheet2.Range("K1").CurrentRegion.Rows.Count
         
        Sheet1.Range("A1:E1").AutoFilter Field:=2, Criteria1:=Sheet2.Range("K" & CityCount).Value
        
        Sheet2.Range("M1").CurrentRegion.ClearContents
        Set AgentRng = Sheet1.Range("A1:A" & DataCount).SpecialCells(xlCellTypeVisible)
        
        Sheet2.Range("A1").CurrentRegion.ClearContents
        AgentRng.Copy
        Sheet2.Range("A1").PasteSpecial xlPasteAll
        
        iRow = Sheet2.Range("A1").CurrentRegion.Rows.Count
        Set AgentRng = Sheet2.Range("A1:A" & iRow)
        AgentRng.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Sheet2.Range("M1"), Unique:=True
         
        Set Wkb = Workbooks.Add
        For AgentCount = 2 To Sheet2.Range("M1").CurrentRegion.Rows.Count
            
            If AgentCount = 2 Then
                Set Wsh = Wkb.Sheets(1)
            Else
                Set Wsh = Wkb.Sheets.Add(After:=Wkb.Sheets(Wkb.Sheets.Count))
            End If
            
            Wsh.Name = Sheet2.Range("M" & AgentCount).Value
            Sheet1.Range("A1:E1").AutoFilter Field:=1, Criteria1:=Sheet2.Range("M" & AgentCount).Value
            Sheet1.Range("A1").CurrentRegion.SpecialCells(xlCellTypeVisible).Copy
            Wsh.Range("A1").PasteSpecial xlPasteAll
            
            Application.CutCopyMode = False
            
            Application.StatusBar = CityCount & "--" & AgentCount & "--" & Sheet2.Range("M" & AgentCount).Value
        Next AgentCount
        
        Sheet1.AutoFilterMode = False
        
        Wkb.SaveAs "C:\Users\akash\Desktop\VBA Training\Data\" & Sheet2.Range("K" & CityCount).Value & ".xlsx"
        Wkb.Close '' SaveChanges:=True
        
    Next CityCount
    
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    
    MsgBox "Done !!"
End Sub

Akash Vishwakarma

Hi This is Akash Vishwakarma. I am working as software developer. I have knowledge in VBA, SQL Server, Python. I have developed this website from Django Framework.