Posts by Fred12

    I currently have a vba code that performs the function of adding columns but I have had some difficulty with tweaking it to also add and populate rows. This VBA code is in the excel doc that is attached to this post. Please read the request below on what I am looking to achieve

    • I would like a message box to pop up when column E says "Failed" or "Swapped for Upfit" that asks for "How many parts are affected?". Whatever quantity that is put in is automatically subtracted from the Qty (Column D) for that part. After that, a new row with the same info should be added right below with the qty of parts affected. For example, row 8 has "10" in qty (Column D) and I select "failed" in column E. A message box will pop up and I input "2" as the number of parts affected. Then the number of quantity in row 10 column D turns to 8, and two rows are added below. Both will be a copy and paste of row 8 Columns A - C but both will have "2" as quantity (Column D). There are now 3 rows involved, rows 8, 9 and 10. Row 8 will have "On Engine", row 9 will have either "failed" or "Swapped for upfit" based off what was initially selected and row 10 will have "On engine"
    • Once that has been done, for row 9, column F should stop updating. It should stay on that number
    • Row 10, column F should start from where row 9 stopped. For example, the part had 40 hrs before it failed.
    • I would also want an option to hide/show columns at the click of a button. By the click of the button, a message box should pop up asking which of the populated columns I want to hide/show

    This is what I would like the code to do. Please let me know if you have any questions. I also attached a documents that has comments on how the code should work. Hope this explains everything well
    Thank you

    Thanks a lot Sktneer for helping with this. The code worked as I wanted it to. However, I have been trying to add some features to the code but I have had some difficulties trying to do that.

    • I added a new column as seen in the attached document for the "Part status". I would like a message box to pop up when column E says "Failed" or "Swapped for Upfit" that asks for "How many parts are affected?". Whatever quantity that is put in is automatically subtracted from the Qty (Column D) for that part. After that, a new row with the same info should be added right below with the qty of parts affected. For example, row 8 has "10" in qty (Column D) and I select "failed" in column E. A message box will pop up and I input "2" as the number of parts affected. Then the number of quantity in row 10 column D turns to 8, and two rows are added below. Both will be a copy and paste of row 8 Columns A - C but both will have "2" as quantity (Column D). There are now 3 rows involved, rows 8, 9 and 10. Row 8 will have "On Engine", row 9 will have either "failed" or "Swapped for upfit" based off what was initially selected and row 10 will have "On engine"
    • Once that has been done, for row 9, column F should stop updating. It should stay on that number
    • Row 10, column F should start from where row 9 stopped. For example, the part had 40 hrs before it failed.
    • I would also want an option to hide/show columns at the click of a button. By the click of the button, a message box should pop up asking which of the populated columns I want to hide/show

    This is what I would like the code to do. Please let me know if you have any questions. I also attached a documents that has comments on how the code should work. Hope this explains everything well
    Thank you

    I need help coming up with the code that adds a new column based off different conditions listed below. I have attached an excel file that I will use to explain what I am looking for.

    • "Sheet1" will be the affected sheet that needs the code
    • On the click of a button, a new column should be added and it's header title should be whatever is in B1, hours. For example if B1 is "Low" the new column added should have a header saying "Low hours" in F6
    • The column added should start from Column F
    • The header for the new column should be at F6
    • On the click of the button, a new column should always be added with the header title described
    • If there will be a new column that would have the same header as a previous column, an option to decide if this a duplicate or a new test. If it's a duplicate, then the old column should be selected but if it's a new test, then a new column should be created. However, the new column should have some kind on separator. For example, instead of having two columns with headers "Low hours", the second column should be "Low hours 2"
    • On the back end there should be a column that keeps a total of all the hours collected by rows. This will be in column E. The total should automatically be updated as other test hour columns are updated

    Thanks in advance for helping with this. Please let me know if you have any questions.

    I need help coming up with the code that creates a new sheet in excel with the click of link on one sheet. I have attached an excel file that I will use to explain what I am looking for.

    • Column E will be the column that hosts the link to the picture tab for every row
    • At the click of the link in column E, a new sheet will automatically be added to the excel document
    • The name of this new sheet will be correspondent to column A and B for the same row. For example, if I click the link in E2, the name of the new sheet that will be added to the document will be A2, B2 i.e. "Book, Florida"
    • The new sheet that has now been added will automatically have a link on its page that will revert me back to the original sheet.

    Please let me know if this explains what I need. Thanks in advance and let me know if you have any questions

    This is wonderful! You really covered all the details with this new code.


    The only thing I noticed was that a new document opened when I clicked the merge button with the merged file. I don't want a separate document to open with the merged file. Can this merge occur in the page shown in the picture attached?


    As you can see in the picture I attached, that is the document that hosts the code for the merge. I want the merge to occur on the "BOM" sheet so this is where the new and merged list will be. So the new and refined list will be on the "BOM" sheet instead of automatically opening a new sheet with the new list. So it will be like pasting the "Doc2" onto "Doc1" and arranging it accordingly to the different sub-headers.


    Please let me know if this helps. Besides that every other thing was pin point. Thanks for helping.

    Hello,


    Thanks a lot for helping and putting this together. The code works as wanted but there is just a little tweak I would like to make and that's on me because I didn't clarify in my description of what I wanted.


    When the merge happens,

    • I don't want a new document to open with the merged file. Instead I want the merge to happen on "Doc1" which is the document that has the code in it. So the info from "Doc2" is merged and joined unto "Doc1".
    • Also, the real document that this will go on will have multiple sheets within that one document. For example, "Doc1" will have different sheets within it but the "BOM" sheet will contain that list that needs to be merged. Can this code work in such a way that it only merges that sheet with the other document?
    • Lastly, can you type out some instructions on how you were able to use the hyperlink to run the code?


    Once again thanks for helping with this. Please let me know if you have any questions.

    I attached a copy of a sample of how I want the system to work. The first sheet "Doc1" represents the main document, "Doc2" represents the second document that will be merged onto "Doc1". Please keep in mind that these are separate document but for explanation purposes, I put them in the same document. The "MergedDoc" sheet represents what I want the document to look like after the merge has occurred. For this merge to occur;

    • The click of a button on "Doc1" should trigger that action. Both documents will be placed in the same folder on my computer.
    • As seen in the merged doc, the merge should occur in an alphabetical order using the "Option" column.
    • If a part shares the same "Option" and "Part name" on both documents but different "Part number", then the one on "Doc2" replaces the one on "Doc1"
    • If a part shares all the same info on both document, then that part doesn't have to be merged.

    All these conditions listed have been illustrated in the excel document attached to this.


    Please let me know if this helps and if you need more information. Thanks in advance.

    I attached a copy of a sample of how I want the system to work. The first sheet "Doc1" represents the main document, "Doc2" represents the second document that will be merged onto "Doc1". Please keep in mind that these are separate document but for explanation purposes, I put them in the same document. The "MergedDoc" sheet represents what I want the document to look like after the merge has occurred. For this merge to occur;

    • The click of a button on "Doc1" should trigger that action. Both documents will be placed in the same folder on my computer.
    • As seen in the merged doc, the merge should occur in an alphabetical order using the "Option" column.
    • If a part shares the same "Option" and "Part name" on both documents but different "Part number", then the one on "Doc2" replaces the one on "Doc1"
    • If a part shares all the same info on both document, then that part doesn't have to be merged.

    All these conditions listed have been illustrated in the excel document attached to this.


    Please let me know if this helps and if you need more information. Thanks in advance.

    Hello Jolivanes, I am trying to create a situation whereby the click of a button in excel document will trigger an action. This action is to create a merge between this document and another document. Both documents consist of data in a tabular manner and are similar in characteristics. You already came up with a system that merges two excel documents excellently but I would like it to work in a specific manner. The code should use columns A and C when merging both docs. If the letters in columns A and C match then they should be merged together. And the new data coming into the main document should have a different font color, say red.


    When I say merge, I mean copy from one document and paste on the other.


    Can this be done?

    Thanks a lot for that Mumps. I did a little touch up and the code worked as I wanted


    However how do I run two macros at the same time on the worksheet code module?


    The first code is



    And the second code is


    Code
    Private Sub Worksheet_Change1(ByVal Target As Range)
        If Intersect(Target, Range("L:L")) Is Nothing Then Exit Sub
        Select Case Target.Value
            Case "Failed", "Swapped or Upfit", "Others"
                Range("A" & Target.Row & ":C" & Target.Row).Copy Sheets("Failed_Replaced").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
                Sheets("Sheet2").Cells(Rows.Count, "E").End(xlUp).Offset(1, 0) = Range("K" & Target.Row)
        End Select
    End Sub


    Thanks again