Q: How can I split data into multiple workbooks/files based on column in Excel?

Question

I'm very new to VBA so I hope I don't sound too ignorant. Each month I receive a report that contains data in the ranges A:T and about 7000-10000 rows. I need to separate this data into multiple workbooks/files so that I can send them out.

Currently, I manually filter the column and copy + paste the data into a blank excel and save as for each name but that is just insanely inefficient. I'm completely new to VBA or any sort of code so I've been scouring all over to find any that might help. I'm not sure if I can directly filter the data and save them into new workbooks but I am aware that you can do it as worksheets instead. I've come close using code from here: https://www.excelhow.net/split-data-into-multiple-worksheets-based-on-column.html as shown below but I've noticed that the character limitation of worksheet names causes some issues.

Sub parse_data()
    Dim lr As Long
    Dim ws As Worksheet
    Dim vcol, i As Integer
    Dim icol As Long
    Dim myarr As Variant
    Dim title As String
    Dim titlerow As Integer

    'This macro splits data into multiple worksheets based on the variables on a column found in Excel.
    'An InputBox asks you which columns you'd like to filter by, and it just creates these worksheets.

    Application.ScreenUpdating = False
    vcol = Application.InputBox(prompt:="Which column would you like to filter by?", title:="Filter column", Default:="3", Type:=1)
    Set ws = ActiveSheet
    lr = ws.Cells(ws.Rows.Count, vcol).End(xlUp).Row
    title = "A1"
    titlerow = ws.Range(title).Cells(1).Row
    icol = ws.Columns.Count
    ws.Cells(1, icol) = "Unique"
    For i = 2 To lr
        On Error Resume Next
        If ws.Cells(i, vcol) <> "" And Application.WorksheetFunction.Match(ws.Cells(i, vcol), ws.Columns(icol), 0) = 0 Then
            ws.Cells(ws.Rows.Count, icol).End(xlUp).Offset(1) = ws.Cells(i, vcol)
        End If
    Next

    myarr = Application.WorksheetFunction.Transpose(ws.Columns(icol).SpecialCells(xlCellTypeConstants))
    ws.Columns(icol).Clear

    For i = 2 To UBound(myarr)
        ws.Range(title).AutoFilter field:=vcol, Criteria1:=myarr(i) & ""
        If Not Evaluate("=ISREF('" & myarr(i) & "'!A1)") Then
            Sheets.Add(after:=Worksheets(Worksheets.Count)).Name = myarr(i) & ""
        Else
            Sheets(myarr(i) & "").Move after:=Worksheets(Worksheets.Count)
        End If
        ws.Range("A" & titlerow & ":A" & lr).EntireRow.Copy Sheets(myarr(i) & "").Range("A1")
        'Sheets(myarr(i) & "").Columns.AutoFit
    Next

    ws.AutoFilterMode = False
    ws.Activate
    Application.ScreenUpdating = True
End Sub

I was wondering if anyone can help me split the data into multiple workbooks based on the Name column (C) or bypass/avoid the character limitation that the worksheet name has, save the worksheets as separate workbooks and rename them later? The files that I send out have the name in the title (eg. NameXYZ_report) so preferably the outcome would have it named, based on the column as well.

Summary of the questions:

1. Split data into multiple workbooks directly based on Name (Column C value, often exceeds 31 characters) with the file name as ‘Name_report0122’ while keeping header (row 1)
2. Keep column width of original data

Edit; If it's not possible to save them directly as workbooks, would it be possible to save them as worksheets in a shortened form of the names, save those worksheets as workbooks and then mass rename the files properly afterwards?

I apologise for any confusion caused by my questions as I am new to this but I want to improve. Thank you all!

Answer 1

Export Filtered Data to a New Workbook

Option Explicit

Sub ExportToWorkbooks()
    
    Const aibPrompt As String = "Which column would you like to filter by?"
    Const aibtitle As String = "Filter Column"
    Const aibDefault As Long = 3
    
    Dim dFileExtension As String: dFileExtension = ".xlsx"
    Dim dFileFormat As XlFileFormat: dFileFormat = xlOpenXMLWorkbook
    Dim dFolderPath As String: dFolderPath = "C:\Test\"
    
    If Right(dFolderPath, 1) <> "\" Then dFolderPath = dFolderPath & "\"
    If Len(Dir(dFolderPath, vbDirectory)) = 0 Then Exit Sub ' folder not found
    If Left(dFileExtension, 1) <> "." Then dFileExtension = "." & dFileExtension
    
    Application.ScreenUpdating = False
    
    Dim sCol As Variant
    sCol = Application.InputBox(aibPrompt, aibtitle, aibDefault, , , , , 1)
    If Len(CStr(sCol)) = 0 Then Exit Sub ' no entry
    If sCol = False Then Exit Sub ' canceled
    
    Dim sws As Worksheet: Set sws = ActiveSheet
    If sws.AutoFilterMode Then sws.AutoFilterMode = False ' turn off AutoFilter
    Dim srg As Range: Set srg = sws.Range("A1").CurrentRegion
    Dim srCount As Long: srCount = srg.Rows.Count
    If srCount < 3 Then Exit Sub ' not enough rows
    Dim srrg As Range: Set srrg = srg.Rows(1) ' to copy column widths
    Dim scrg As Range: Set scrg = srg.Columns(sCol)
    Dim scData As Variant: scData = scrg.Value
    
    ' Write the unique values from the 1st column to a dictionary.
    
    Dim dict As Object: Set dict = CreateObject("Scripting.Dictionary")
    dict.CompareMode = vbTextCompare ' case insensitive
    
    Dim Key As Variant
    Dim r As Long
    
    For r = 2 To srCount
        Key = scData(r, 1)
        If Not IsError(Key) Then ' exclude error values
            If Len(Key) > 0 Then ' exclude blanks
                dict(Key) = Empty
            End If
        End If
    Next r
    If dict.Count = 0 Then Exit Sub ' only error values and blanks
    Erase scData
    
    Dim dwb As Workbook
    Dim dws As Worksheet
    Dim dfcell As Range
    Dim dFilePath As String
    
    For Each Key In dict.Keys
        ' Add a new (destination) workbook and reference the first cell.
        Set dwb = Workbooks.Add(xlWBATWorksheet) ' one worksheet
        Set dws = dwb.Worksheets(1)
        Set dfcell = dws.Range("A1")
        ' Copy/Paste
        srrg.Copy
        dfcell.PasteSpecial xlPasteColumnWidths
        srg.AutoFilter sCol, Key
        srg.SpecialCells(xlCellTypeVisible).Copy dfcell
        sws.ShowAllData
        dfcell.Select
        ' Save/Close
        dFilePath = dFolderPath & Key & dFileExtension ' build the file path
        Application.DisplayAlerts = False ' overwrite without confirmation
        dwb.SaveAs dFilePath, xlOpenXMLWorkbook
        Application.DisplayAlerts = True
        dwb.Close SaveChanges:=False
    Next Key
    
    sws.AutoFilterMode = False
    Application.ScreenUpdating = True
    
    MsgBox "Data exported.", vbInformation
    
End Sub