• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Create workbooks based on values in 2 column values

Hello,

Need help to create coding to generate excel workbooks based on column "D" and column "L". I have attached the excel sheet with the data.

For example, 1st excel file should be created for BA (column D) and Direct (column L), then BA and Indirect.

The attached coding will consider only one column value (advanced filter).
Code:
Sub ListCreation()
    Application.ScreenUpdating = False
    On Error Resume Next
    With Sheets("Data")
        m_max = .Cells(.Rows.Count, 13).End(xlUp).Row
        .Range("M1:M" & m_max).Clear
        k_max = .Cells(.Rows.Count, 12).End(xlUp).Row
        .Range("D1:D" & k_max).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=.Range("M1"), Unique:=True
        m_max = .Cells(.Rows.Count, 13).End(xlUp).Row
        For m = 2 To m_max
            CRI = .Cells(m, 13)
            chk = Evaluate("ISREF('" & CRI & "'!D1)")
            If Not chk Then Sheets.Add(After:=Sheets(Sheets.Count)).Name = CRI
            .Range("A1:L" & k_max).AutoFilter
            .Range("A1:L" & k_max).AutoFilter Field:=4, Criteria1:=CRI
            .Range("A1:L" & k_max).Copy
            With Sheets(CRI).Range("A1")
                .PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
                .PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
                .Select
                Columns.AutoFit = True
                ActiveWindow.DisplayGridlines = False
            End With
           
        Next m
        .Select
        .Range("A1:J" & k_max).AutoFilter
        .Range("J1:J" & m_max).Clear
    End With
    Application.ScreenUpdating = True
End Sub
Your help would be mucha appreciated.
 

Attachments

  • Data.xlsx
    607.8 KB · Views: 5
Sure I have the file now I send it to you.
You will need to create a folder on you c drive and name it "TestFolder". I have the code set to look there. You can change to it.
 

Attachments

  • Data.xlsm
    618.4 KB · Views: 4
HI,
The code in the workbook is the same as the code you posted. I modified it so that it will create the workbooks. I mis spoke about the "Test File". I have the code set for "Thisworkbook" path.
 
HI,
The code in the workbook is the same as the code you posted. I modified it so that it will create the workbooks. I mis spoke about the "Test File". I have the code set for "Thisworkbook" path.
I appreciate your efforts to support me.

I ran the code but it does not create sheets based on the 2 criterias mentioned in the initial request. Am I missing something here?

The first file generated for 5A but it includes both Direct and Indirect. I am trying to get the files generated for both Direct and Indirect which tagged to 5A (column D).
 
Hi,Do you want the "Direct" and "BA" in its own workbook and then have a work book for "BA" indirect?
Charles
 
Last edited:
HI,
I modified the code. I misspelled the work sheet for "Indirect".
I added a button that you now click and have the code run.

Charles
 

Attachments

  • Data2workbooks.xlsm
    623.8 KB · Views: 4
Back
Top