1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. 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...

  3. 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

Discussion in 'VBA Macros' started by Niranjanrajrishi, Oct 5, 2018.

  1. Niranjanrajrishi

    Niranjanrajrishi Member

    Messages:
    61
    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 (vb):
    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.

    Attached Files:

  2. charlesdh

    charlesdh New Member

    Messages:
    29
    Hi,
    Not sure if you have this issue resolved. If not let me know. I will try to help on this.
  3. Niranjanrajrishi

    Niranjanrajrishi Member

    Messages:
    61
    Thank you...since I haven't got a perfect way, I used very long way with additional columns and formulas. If you can help me with this, it would be a great help.
  4. charlesdh

    charlesdh New Member

    Messages:
    29
    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.

    Attached Files:

  5. Niranjanrajrishi

    Niranjanrajrishi Member

    Messages:
    61
    Thank you so much. I will test it and let you know.
  6. charlesdh

    charlesdh New Member

    Messages:
    29
    Hi,
    Just to follow up. Did the file I modified for you work?
  7. Niranjanrajrishi

    Niranjanrajrishi Member

    Messages:
    61
    Apologizes for the delay to come back to you. Where did you save the coding? I opened the file which I see the same coding and it does have any coding to select the folder "TestFolder".
  8. charlesdh

    charlesdh New Member

    Messages:
    29
    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.
  9. Niranjanrajrishi

    Niranjanrajrishi Member

    Messages:
    61
    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).
  10. charlesdh

    charlesdh New Member

    Messages:
    29
    Hi,Do you want the "Direct" and "BA" in its own workbook and then have a work book for "BA" indirect?
    Charles
    Last edited: Oct 29, 2018
  11. Niranjanrajrishi

    Niranjanrajrishi Member

    Messages:
    61
    Yes...thats the perfect thought. I am trying to create sheets based on 2 column values. So BA Direct and BA Indirect should create 2 workbooks.
  12. charlesdh

    charlesdh New Member

    Messages:
    29
    Hi,
    See if this is what you wanted.

    Charles

    Attached Files:

    Niranjanrajrishi likes this.
  13. Niranjanrajrishi

    Niranjanrajrishi Member

    Messages:
    61
    I ran the code and it worked for Direct cases. For Indirect, new workbood did create but all workbooks were emplty. No data copied to Indrect workbooks.
  14. charlesdh

    charlesdh New Member

    Messages:
    29
    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

    Attached Files:

    Niranjanrajrishi likes this.
  15. Niranjanrajrishi

    Niranjanrajrishi Member

    Messages:
    61
    Thank you so much! It worked perfectly.
  16. charlesdh

    charlesdh New Member

    Messages:
    29
    Thanks for letting me know.

    Charles

Share This Page