[VBA] How to split data into multiple workbooks & retain the format

[zeromax1]

Hello everyone. I would like to ask for your help. I want to split the data into multiple workbooks by PIC. (please see the following cap screen) It means each file contains the unique data of each PIC. The format also will be kept for each new workbook. The new workbook name will be the "origin file name - (PIC)" and place at the same path of the origin file.

Besides that, the whole page of "Code Table" will be copy to these new workbook at the same time.

Please download my demo: Trail Split Sheet2.xlsx

The format should be kept in each new workbook. "

I have already refer to this post (VBA Split One Worksheet Into Multiple Workbooks and Retain Formatting) and try to modify the code by myself. Unfortunately, the code only applicable to the Column A with number.

 

[zeromax1]

It's the fileformat number specifying to save as an xlsx file.

Hello Fluff, Are there possible to add one more column for the criteria?
Eg. I want to split the files base on Column A & B.

I attached my VBA code:

Sub Split()
   Dim cl As Range
   Dim WS As Worksheet
   Dim rng As Range
    
   Application.ScreenUpdating = False
  
   Set WS = ThisWorkbook.Worksheets(1)
   Filename = ThisWorkbook.Name
   If InStr(Filename, ".") > 0 Then
        Filename = Left(Filename, InStr(Filename, ".") - 1)
    End If
    
   If WS.FilterMode Then WS.ShowAllData
   With CreateObject("scripting.dictionary")
      For Each cl In WS.Range("A9", WS.Range("A" & Rows.Count).End(xlUp))
         If Not .Exists(cl.Value) Then
            .Add cl.Value, Nothing
            WS.Copy
            Range("A8").AutoFilter 1, "<>" & cl.Value
            Range("A9:A3000").SpecialCells(xlVisible).EntireRow.Delete
            ActiveSheet.ShowAllData
            ActiveWorkbook.SaveAs ThisWorkbook.Path & "\" & Filename & " " & "(" & cl.Value & ")" & ".xlsx", 51
            ActiveWorkbook.Close False
         End If
        
                
      Next cl

   End With
End Sub

 

[Fluff]

Can you please start a new thread for this, thanks.