Posts by gijsmo

    Without knowing exactly which columns are affected you could add some code to try and convert any numbers stored as text eg:

    For Each rCell In ThisWorkbook.Sheets(SourceSheetName).UsedRange
       rCell.Value = rCell.Value
    Next rCell

    This would need to be added after data is copied from the recordset.

    Please note also that you may need to do some data correction. I noticed there were records in the Alldata sheet that will not match to any of the sub-categories specifically, "Handi Bus Driver" (this should probably be "Handibus Driver" by the look of it) and also "Recreation"

    Also, I happened to notice that eg "Parks Manager" and "Recreation Programming Manager" appear under both Community Services and Senior Management which means any entries for these will be duplicated across the 2 corresponding sheets. I can see some others, this may be intentional.

    My sample code is attached which should do what you are trying to do if I have interpreted everything in these posts so far :)

    There are 2 main macros called TransferData and ClearSheets. The Transferdata macro does the following:

    1. Read each of the 7 main categories in the Needed Info sheet, one column at a time
    2. Find the matching rows in the Alldata sheet for all the values in the category (eg, Community Services has 26 sub-categories, Executive has 3 etc)
    3. Copy the matching data to the corresponding category sheet (excluding B, C, E, G, H, K, L, M, N, Q columns)
    4. Minor formatting (auto column widths, freeze panes)
    5. Sort the data by Title and Name in ascending order

    This means every time you run the macro, the data is recreated on the category sheets. As your sample data is limited, some sheets will end up with just a header row with no actual data rows when you run the Transferdata macro.

    ClearSheets can be used if required, it will just delete everything in the category sheets. Not strictly required as the TransferData macro will do this anyway. I have added another button on the Summary sheet that will run this macro.


    This sounds similar to my reply to your other post:

    Search for partial value

    So try changing:

    Sheets("Worksheet").Cells(y, 3).Value = txtReceived
    Sheets("Worksheet").Cells(y, 4).Value = txtMoved
    Sheets("Worksheet").Cells(y, 5).Value = txtCompleted


     Sheets("Worksheet").Cells(y, 3).Value = DateValue(Format(txtReceived, "mm/dd/yyyy"))
     Sheets("Worksheet").Cells(y, 4).Value = DateValue(Format(txtMoved, "mm/dd/yyyy"))
     Sheets("Worksheet").Cells(y, 5).Value = DateValue(Format(txtCompleted, "mm/dd/yyyy"))

    In terms of your other request:


    I have a list box and I want to be able to sort it by clicking or double clicking on the header of each column.

    I assume you are referring to the data starting at A5 on the sheet called Worksheet.

    If so, I've added a Worksheet_BeforeDoubleClick event to the Sheet1 code which should enable to sort by double clicking on any of the headers in A5 to G5.

    Sample is attached.

    Possible Tracking Number Database Version 3-sort cols.xlsm

    You are welcome, there are many ways to do the same thing in Excel.

    I was taught a long time ago to write code to make it as easy as possible to follow and maintain.

    You can take this many steps further as required eg, you could actually write code to find the column with the specific header containing the Yes/No values. This would make it easier again to add more data.

    In my code sample I've tried to make this manageable as follows.

    First there is this line of code:

      'this is where the header row is assumed to be on both sheets
      Const StartRow = 3

    This points to the header row on both the Active and Archive sheets.

    Second this line of code:

    'column P is assumed to contain the Yes/No values
    rSrc.AutoFilter Field:=.Range("P" & StartRow).Column, Criteria1:="Yes"

    So if P3 has now become say R4 you would change to:

      Const StartRow = 4


    rSrc.AutoFilter Field:=.Range("R" & StartRow).Column, Criteria1:="Yes"

    Also, the following line of code:

    Set rSrc = .Range("A" & StartRow).CurrentRegion

    will automatically cater for more columns (and rows).

    My aim is to make changes relatively simple to implement.

    OK, there is more information in the new example book but it's still not quite clear how you want to split up the data.

    You have only 7 main departments and there is a sheet for each:

    However, you seem to want to somehow use Column P to split up the data into those 7 sheets.

    In the new example sheet you attached, the following are the unique values in Column P on the Alldata sheet:

    So there seems to be a "lookup table" missing eg which of the 7 sheets does the data for Engineering, Human Resources, IST, etc actually need to be copied to. Some rows in the sample do have a matching sheet name already eg Marketing_Communications.

    What I was excepting see was something like a table with all the sub-categories on the left and the corresponding sheet name on the right eg:

    The left side would of course have to include all possible sub-categories, the above example is just derived from your sample.

    The coding is not too difficult but without a table like this it is difficult to start using the sample data in Column P.

    Also, as per previous post, you also mention "the data needs to be appended to the last row on the appropriate sheet" when you run the macro. However, you either then need to remove the data from the Alldata sheet or add another column that flags that the data has been copied otherwise the data will get copied again if you run the macro more than once.

    You now also say "I will end up removing the B, C, E, G, H, K, L, M, N, Q columns before distributing the data to other sheets." but this could be relatively easily done as part of the macro that copies the data to the 7 sheets.

    I assume you are referring to the following bit of code:

            .Cells(lr + 1, "C").Value = Me.txtReceived.Value
            .Cells(lr + 1, "D").Value = Me.txtMoved.Value
            .Cells(lr + 1, "E").Value = Me.txtCompleted.Value

    If so, maybe try changing this to :

            .Cells(lr + 1, "C").Value = DateValue(Format(Me.txtReceived.Value, "mm/dd/yyyy"))
            .Cells(lr + 1, "D").Value = DateValue(Format(Me.txtMoved.Value, "mm/dd/yyyy"))
            .Cells(lr + 1, "E").Value = DateValue(Format(Me.txtCompleted.Value, "mm/dd/yyyy"))

    Also, make sure the cells you are writing to are formatted with the "mm/dd/yyyy" format.

    For a partial string match anywhere in the string maybe try the following:

    This will of course match anywhere in the string, if you specifically want to only match the end of the string you could try:

    Okay, that helps a bit.

    I can see there are 7 sheets from Community Services through to Senior Management. However, your columns V to X only seem to cover the first 3 of these.

    I am assuming the list of names under each of the columns V to X contain the "sub-categories" you were referring to. But it's not entirely clear where these sub-categories can be found in the main data. Also, I would have expected 7 of these in total with their sub-categories listed in full eg, the Executive column has no sub-categories listed and of course there are no columns for the others eg, Infrastructure, etc.

    Column P seems to mostly represent the "Primary Division" which I've assumed would correspond to 1 of the 7 sheet names. However, there are items in the sample in column P like "Engineering" and "FCSS" that seem to be sub-categories but there is no corresponding primary category (and therefore no idea which sheet) these belong to. It seems column P could contain either a primary or sub-category value.

    It may be an idea to hold the full list of the 7 main departments and their sub-categories in another sheet, just to separate it from the main data. Even if it is retained on the Alldata sheet, I would expect the column headers will need to match the sheet names exactly eg, at the moment cell V1 = Comm Services but the corresponding sheet is actually Community Services.

    You also mention "the data needs to be appended to the last row on the appropriate sheet" when you run the macro. However, you either then need to remove the data from the Alldata sheet or add another column that flags that the data has been copied otherwise the data will get copied again if you run the macro more than once.

    You mention A:R contains the data but the code is also looking at V2:V11 - what does V2:V11 contain ?

    If it contains main department name then that would be more than the 7 sheets you mention.

    When you say sub-category, is there a column for main department in A:R and another column representing sub-category?

    Your autofilter uses a variable called strSearch but the code provides no idea of what this value could be.

    A sample workbook with some (non-sensitive) data and an example of what the output should look like is always helpful.

    Sorry but I cannot see any real reason why the number of files would make any difference other than time to process.

    Each file is opened, data is extracted and then the file is closed so there is no reason I can see why this should not work on a large number of files.

    The attached file should meet the requirements. It is a stand-alone file containing macros that will open the files in the nominated folder, extract the cell values from the PreTerm sheet and write these values to a new workbook which will be saved in the nominated output folder.

    The main macro is called ReadPreTermFiles and can be launched by the Extract Data button.

    The output filename is coded in the macro, currently this is in the format of "PreTerm Output dd-mmm-yy" but obviously this can be changed.

    Please note there is only minimal checking in the macro (file/folder exists, etc).


    You are welcome.

    I have updated the attached sheet which will color the rows on the SUMMARY sheet as red/blue as you requested (you can change these colors in the macro as required).

    As before, I have retained the sheet called SUMMARY-with formulas - this sheet can be deleted, it just shows the formulas used to derive the result and now includes the extra formulas used to help derive the rows to be colored.