Save Multiple Files Excel that has been automatically splitted by a column

[Lidya]

Hello.
Does anyone knows how to solve this?
I have a master data , and i want to split the master data into multiple files which is splitted by a column.
For example :
Master data

Unit	Type A	Type B	Type C
2020	2021	2020	2021	2020	2021
X	1000	2000	2000	2000	2000	2000
Y	2000	1000	1000	1000	4000	4000
X	2000	1000	1000	1000	4000	4000
X	2000	30000	30000	1000	4000	4000
Y	2000	30000	1000	1000	4000	4000
Z	2000	30000	4000	4000	4000	4000
Z	2000	30000	4000	4000	4000	4000

I want to split by column "UNIT", so there will be 3 excel files with files name "X.xls" , "Y.xls", and "Z.xls" and each files contains only the specific unit, like this:
For excel files name "X.xls", will contains only :

Unit	Type A	Type B	Type C
2020	2021	2020	2021	2020	2021
X	1000	2000	2000	2000	2000	2000
X	2000	1000	1000	1000	4000	4000
X	2000	30000	30000	1000	4000	4000

For excel files name "Y.xls", will contains only :

Unit	Type A	Type B	Type C
2020	2021	2020	2021	2020	2021
Y	2000	1000	1000	1000	4000	4000
Y	2000	30000	1000	1000	4000	4000

Can anyone knows how to solve?
I'm not familiar into any macros.. please guide me from the beginning..
thanks a lot!!

 

[HighAndWilder]

Welcome to Mr Excel.

Put this code into a main code module in the workbook containing the data.

If you have not got the Developer option on the ribbon then follow this.
Show the Developer tab - Microsoft Support

Select Developer, Visual Basic, Insert, Module to create a new module.

In the editing window, paste the VBA code.

Change the code to indicate the source worksheet and destination folder where indicated.

To run the code, select Developer, Macros on the ribbon and select 'subSplitData' and Run.

Public Sub subSplitData()
Dim lngLastRow As Long
Dim WsMaster As Worksheet
Dim strPath As String
Dim arr() As Variant
Dim i As Integer

  ActiveWorkbook.Save
  
  ' Turn off screen updating.
  Application.ScreenUpdating = False

  ' Assign worksheet object.
  Set WsMaster = Worksheets("Master") '<<<<< --- CHANGE THE WORKSHEET NAME AS APPROPRIATE
  
  ' Set path where new workbooks are to be saved.
  strPath = ActiveWorkbook.Path & "\" ' <<<<< --- CHANGE IF THE DESTINATION PATH IS NOT TO BE THE SAME.

  ' Assertain the last row of the master worksheet.
  With WsMaster
    lngLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
  End With
  
  ' Loop three times for X, Y and Z.
  For i = 1 To 3
    
    ' Use the worksheet FILTER function to populate an array with the X, Y or Z data.
    arr = Evaluate("FILTER(A2:G" & lngLastRow & ",A2:A" & lngLastRow & "=" & """" & Mid("XYZ", i, 1) & """" & ")")
    
    ' This creates a new single sheet workbook.
    WsMaster.Copy
      
    With ActiveWorkbook
      With .Sheets(1)
        .Name = Mid("XYZ", i, 1) ' Name the worksheet in the new workbook.
        .Range("A2:G" & Rows.Count).Cells.Clear   ' Clear all cells apart from the first row.
        .Range("A2:G" & UBound(arr) + 1).Value = arr ' Write the data in the array to the new worksheet.
      End With
      On Error Resume Next
      Kill (strPath & Mid("XYZ", i, 1) & ".xls") ' Delete the destination workbook if it exists.
      On Error GoTo 0
      .SaveAs strPath & Mid("XYZ", i, 1) & ".xls" ' Save the destination workbook.
      .Close True ' Close the destination workbook.
    End With
  
  Next i ' Loop to next value in X,Y or Z.
  
  ' Turn on screen updating.
  Application.ScreenUpdating = True
  
  MsgBox "New workbooks created.", vbOKOnly, "Confirmation."
  
End Sub