Macro to split data based on the value from specific column

[Ratna Chhetr]

Hello VBA gurus,

I am looking for a macro to split the data based on the value from the specific column.

I have attached my excel file and there is a tab called "Requirement" for more info what I would like to have.

Your help is highly appreciated and thank you in advance.

Best regards,
Ratna

[arnelgp]

i created another sheet (job) and add button to split data in "data" sheet.

[Ratna Chhetr]

Thank you arnelgp, I wanted to create separate workbook (not worksheet) and save into to C:\WIP\GLAD into the folder that has created based on the value from Column H and needs to save into respective folder based on the file created.
Folder I have is as per below (just for example)

601
604
606
610 etc

[Ratna Chhetr]

I also noticed that when I copy macro into original file, it doesn't work. In original file there are few more columns not only A:T

Also I want to lookup Date field instead column L in order to get the file name.

Thank you and I appreciate for your help.

[georgiboy]

If you are on office 365 then perhaps:

Sub test()    
    Dim unq As Variant
    Dim tbl As Object, x As Long
    Dim lc As ListColumn
    Dim wbTmp As Workbook, wsTmp As Worksheet
    
    With Application
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
    End With
    
    Set tbl = Sheet1.ListObjects(1)
    Set lc = tbl.ListColumns("CostCenter")
    With tbl
        .Sort.SortFields.Clear
        .Sort.SortFields.Add Key:=lc.DataBodyRange, Order:=xlAscending
        .Sort.Apply
    End With
    unq = Application.Unique(lc.DataBodyRange, False, False)
    
    For x = 1 To UBound(unq)
        tbl.Range.AutoFilter lc.Index, unq(x, 1)
        Set wbTmp = Workbooks.Add
        Set wsTmp = wbTmp.Sheets(1)
        tbl.Range.SpecialCells(xlVisible).Copy
        With wsTmp
            .Range("A1").PasteSpecial xlPasteValues
            .Range("A1").PasteSpecial xlPasteFormats
            .Cells.EntireColumn.AutoFit
        End With
        Application.CutCopyMode = False
        With wbTmp
            .SaveAs "C:\WIP\GLAD\" & unq(x, 1) & Format(Date, "MMMYYYY"), 51
            .Close False
        End With
    Next x
    
    With Application
        .ScreenUpdating = True
        .Calculation = xlCalculationAutomatic
    End With
End Sub

[Ratna Chhetr]

Thank you georgiboy, It runs on the file I attached. But I tried the macro into my original file but got debug on below line. In my original file data contain through A to U column, I hope this won't be an issue. I wanted to get the file name from the Date column not just the current month.

Set tbl = Sheet1.ListObjects(1)

[georgiboy]

Maybe try the attachment:

The folder 'C:\WIP\GLAD' should already exist

[arnelgp]

here check it again.

[arnelgp]

final revision.

[Ratna Chhetr]

Thank you, looks like it works but there is one problem. File name for new workbook is picking from Column A. It should be picked from "CostCenter" field.

[Ratna Chhetr]

It only creates a folder but there is no file inside respective folder.

[arnelgp]

here is another try.
now looking at "CostCenter" column for the cost center.

[Aussiebear]

How often does Columns A & H differ?

[Ratna Chhetr]

In original data, Column H has numeric info but column A has Alfa numeric data.

[Aussiebear]

In original data, Column H has numeric info but column A has Alfa numeric data.

However in your sample file of over 23,000 rows there is no Alpha Numeric data in Column A, its all Numeric. The responses provided were based on the sample file provided.

[Ratna Chhetr]

In my original file there are alfa numeric value with over million rows. That's why wanted to split based on the value from Column H.

[georgiboy]

Did you get errors from post 7?

[Ratna Chhetr]

I tried in my original file, it only creates a folder (not all the time) and file with Row A only. I couldn't get entire data.

[arnelgp]

post again your "real" data or at least have same table format at the original.

[Ratna Chhetr]

Here you go,
All the blank columns have the data in original file. Original data has over million rows. I am unable to upload due to file size limitation. I wanted to split by Column G.