Posts by Mumps

    Try:

    Mumps, I just managed to find the code for my requirement via Google. Thank you so much for your help.

    Since I didn't write this code, rather than trying to decipher it, it might be easier for me to help if you could attach copy of your file that includes the single spreadsheet with data and the macro that you currently use that does this splitting for you and names the sheets with the respective column values. Then explain in detail, step by step referring to specific cells, rows, columns and sheets exactly what you want to do.

    Place this macro in a regular module not the ThisWorkbook code module.

    This macro will replace the formulas in columns A and AT of the Data sheet:



    This macro will add all the data to the Output sheet including replacing the formulas:


    For some reason all of the sheets had a used range up to the last column which is 16384 even though they were all blank except for the first two. In the attached file I have deleted all the extra blank columns. Also, in the Splitdatabycol macro, replace this line of code:

    Code
    1. icol = ws.Cells(1, Columns.Count)

    with this line:

    Code
    1. icol = ws.Cells(1, Columns.Count).End(xlToLeft).Column + 1


    See if this makes a difference.

    Quote

    Can we have a code to populate all the sheet names in the first sheet?

    I'm not really sure what you mean. The first sheet already has the sheet names in column A.


    Also, it's hard to see what kind of data needs to be sorted because all the cells in the last column in each sheet in your sample file contain the same data. I think that it would be easier to understand what you want to do if you could attach your actual file with the data de-sensitized if necessary.

    i. One or a List of A : Example : 24148992,24148978,24148953

    ii. One or a List of B : 28-3336-9965-75,644-0086-3476-54

    iii. One or a List of C : P000629440,P000639254

    I don't see any of these values in your sample file. Please attach a file that represents exactly your actual file and explain in detail what you want to do, referring to specific cells, rows, columns and sheets using a few examples from your data. Also, show your expected results in the data tabs.

    Try the attached version. What do you mean by:

    Quote


    the yellow PO, that should not be added, while the lines are being deleted

    Files

    • tr1face.xlsb

      (30.56 kB, downloaded 15 times, last: )

    Try the attached file. I have modified the Invoice_Template to unmerge the merged cells. You should avoid using merged cells at all cost as they almost always create problems for macros. Click the button on the Data sheet.

    Files

    • tr1face.xlsb

      (26.6 kB, downloaded 17 times, last: )

    Do you want to sort by first name or last name? If by last name, then separate the first and last names in sheet 2 into separate columns, first name in column A and last name in column B and attach an updated file. Do you want the drop down in A1 or do you want the header "Name" in A1 and the drop down in A2 below the header?