Copy column Value from one sheet to another and delete duplicates

  • Hello everyone. Found this site and one old thread caught my eye. I'm trying to do something similar to what this person was doing except I am trying to figure out how to make it post the Value from the columns and not just the cell data itself. In a nut shell I am trying to automate a log of everything generated but I am missing something I am sure. I'm running Office365.

    The formulas I am using will be as follows.

    I use this one for generating the serial numbers based on criteria in the boxes I have linked.

    Code
    1. =G2&H2&I2&TEXT(SEQUENCE(J2),"000")

    And I use this one to link two cells together for a datamatrix barcode that gets printed on a label. It helps to link the part number and serial number together while placing a space between. It's this DataMatrix cell values that I need to store and not the formula.

    Code
    1. =C2&" "&A2

    This is the old thread I found. It was quite informative and helped me with some other things that I was messing around with at home. Attached is the format that I am working with more or less. I have two Workbooks I am trying to do this particular capture of values with. One has 9 sheets that I need to link and the other has 8. Is what I am doing even possible and I am over complicating it or is there a more simplistic way for me to achieve what I am try to accomplish.ExampleValueCapture.xlsx

  • Hello and Welcome to the Forum :)


    The event macro posted by Smallman does exactly what you are requesting ...


    Code
    1. Private Sub Worksheet_Activate()
    2. Sheet1.Range("A2", Sheet1.Range("A" & Rows.Count).End(xlUp)).Copy Sheet2.Range("A2").End(xlDown)(2)
    3. Sheet2.Range("A2", Sheet2.Range("A" & Rows.Count).End(xlUp)).RemoveDuplicates 1
    4. End Sub



    What do you exactly mean by :

    Quote

    post the Value from the columns and not just the cell data itself ...


    Why don't you take advantage of your sample ... to show the final expected result ... ;)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • I did test it out. It worked for a split second. Then all the cells that the data was on went blank. I proceeded to move to a new cell and got back a syntax error with no result under. I have 3 minutes left at work but I when I get home in a few minutes I will produce what I did here. Believe me, had it worked beyond that I would have been ecstatic! I was amazed by what it did initially though.

  • Hello again,


    Attached is your test file with a tiny modification ...;)


    Hope this will help

    :)

    Files

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Other than having to fix my formula for automatically creating the serial numbers this is exactly what I am looking for!

    If you don't mind I have a couple questions.

    First. The atpvbaen.xks, solver, and the Eurotool. I have neither of these. Will that make a difference?

    Second. As I stated earlier. I have 9 sheets on one workbook and 8 on another. How do I make them do the same thing in the workbook? I'm assuming it'll look something like this assuming I use what I am thinking (sheet 11 on one workbook and sheet10 on another) For this I'll use the sheet10 one.

    Code
    1. Private Sub Worksheet_Activate()
    2. Sheet1.Range("A2", Sheet1.Range("A" & Rows.Count).End(xlUp)).Copy
    3. Sheet10.Range("A1048576").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
    4. Sheet10.Range("A2", Sheet2.Range("A" & Rows.Count).End(xlUp)).RemoveDuplicates 1
    5. Sheet2.Range("A2", Sheet2.Range("A" & Rows.Count).End(xlUp)).Copy
    6. Sheet10.Range("A1048576").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
    7. Sheet10.Range("C2", Sheet10.Range("A" & Rows.Count).End(xlUp)).RemoveDuplicates 1
    8. End Sub

    And so on. Would I be correct in this?

  • Glad to hear it is what you were looking for :)


    Thanks for your Thanks ...AND for the Like :thumbup:


    To quickly answer your two questions


    1. No

    2. Yes ... make sure to add your Worksheet_Activate event in the Destination worksheet

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Just to clarify, and I apologize ahead of time for the extra questions.

    Do I need to place this next code after every 2 sets or can it be done just the once at the end of everything.

    Code
    1. Sheet10.Range("A1048576").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues

    Lastly. By Worksheet_Active. Are you referring to the following code?

    Code
    1. Option Explicit
  • To clarify :


    Private Sub Worksheet_Activate() is the Event macro which needs to be located in the destination sheet module ...


    The first instruction is the Copy Instruction

    Code
    1. Sheet1.Range("A2", Sheet1.Range("A" & Rows.Count).End(xlUp)).Copy

    and the second instruction does the Paste Values portion

    Code
    1. Sheet10.Range("A1048576").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues

    You cannot separate them ...


    Hope this clarifies

    :)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • So by that do you mean this.

    Code
    1. Private Sub Worksheet_Activate()
    2. Sheet1.Range("A2", Sheet1.Range("A" & Rows.Count).End(xlUp)).Copy
    3. Sheet10.Range("A1048576").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
    4. Sheet10.Range("A2", Sheet2.Range("A" & Rows.Count).End(xlUp)).RemoveDuplicates 1
    5. Sheet2.Range("A2", Sheet2.Range("A" & Rows.Count).End(xlUp)).Copy
    6. Sheet10.Range("A1048576").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
    7. Sheet10.Range("C2", Sheet10.Range("A" & Rows.Count).End(xlUp)).RemoveDuplicates 1
    8. End Sub

    Or should it be like this

    I just want to be certain that I format these correctly. I've been busy doing my regular job today so haven't had time to apply this yet. Just now getting a breather and I'm nearly the end of my shift. Again, thank you so much for all your help. If it weren't for finding Smallman post I never would have known this was remotely possible and for your editing it to make it work for what I wanted it to.

  • Already I am having a slight issue. Running into an error. I'm curious if I am simply overloading it. This particular one I am doing is 10 pages with 9 being used in this and 1 used for something else.


  • From your different explanations, cannot determine if an Event macro is precisely what you truly need ...


    Could you attach a sample workbook which properly illustrates all your constraints ... and your expected result ..

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Now that I am at work I can give you what I have been working on in full.

    The one I gave here was more of a concept but much more simplistic.

    The file I am attaching is the actual file I have been trying to make this work. In the code cell is what I am trying to make work. (Sheet 9 is not used for anything but validation of the data matrix vs physical so nothing is used on that sheet.)

    Files

  • You have added a password which prevents any body from accessing your sheets ...;)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Based on what I have been experimenting with the macro given I can do 1 sheet with as many Columns as I feel like until I somehow make it cap out on memory. (I haven't tried and find the attempt unnecessary given I only need up to 3 maximum Columns per Sheet.)

    So this definitely works and very well.


    How ever if I try to add a second sheet it starts throwing errors.


    So it definitely works and exceedingly well. I just can't quite figure out how to make it work beyond 1 Sheet.

    If I had to take a guess based on observations, I would say that it is trying to pull from both sheets at the same time rather than just pulling from one sheet at a time thus conflicting with one another. If there were a way to make it take turns I think this would be still usable.

  • Figured it out. There were two issues I needed to resolve. The first was I had the Columns protected in Sheet10 Cells1. This was causing a debugging to pop up because it was trying to write over those spots. Not a big deal. I just took protection away from that individual worksheet. Not a huge loss since it's just a log.

    The second issue was with the cells starting code.


    I needed to choose the sheet and pick "Change" as the code. This allowed me to use multiple sheets in order instead of them all trying to copy and past at the same time.


    Thank you so so so much for all your help. I just wanted to let you know I found a solution to the errors I was getting and I am very grateful!

  • Glad to hear you could fix your problem :)


    Thanks for your Thanks ... AND for the Like :thumbup:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Go figure now that I complete this project that I think of more to try. Since I allowed access to this an individual has already messed up my log. So this leads me to a new twist on this one. Is there a a way to have this work but on a different sheet?

    Example:

    Person creates something in Workbook1.

    VBA code pulls what they create to Workbook2 instead of it using a sheet on WB1


    Workbook2 would always be open on my desktop.