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.

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

=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

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.

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.

Private Sub Worksheet_Activate()
 Sheet1.Range("A2", Sheet1.Range("A" & Rows.Count).End(xlUp)).Copy
 Sheet10.Range("A1048576").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
 Sheet10.Range("A2", Sheet2.Range("A" & Rows.Count).End(xlUp)).RemoveDuplicates 1
 Sheet2.Range("A2", Sheet2.Range("A" & Rows.Count).End(xlUp)).Copy
 Sheet10.Range("A1048576").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
 Sheet10.Range("C2", Sheet10.Range("A" & Rows.Count).End(xlUp)).RemoveDuplicates 1
End Sub

And so on. Would I be correct in this?

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.

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

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

Option Explicit

So by that do you mean this.

Private Sub Worksheet_Activate()
 Sheet1.Range("A2", Sheet1.Range("A" & Rows.Count).End(xlUp)).Copy
 Sheet10.Range("A1048576").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
 Sheet10.Range("A2", Sheet2.Range("A" & Rows.Count).End(xlUp)).RemoveDuplicates 1
 Sheet2.Range("A2", Sheet2.Range("A" & Rows.Count).End(xlUp)).Copy
 Sheet10.Range("A1048576").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
 Sheet10.Range("C2", Sheet10.Range("A" & Rows.Count).End(xlUp)).RemoveDuplicates 1
End Sub

Or should it be like this

Option Explicit

Private Sub Worksheet_Activate()
 Sheet1.Range("A2", Sheet1.Range("A" & Rows.Count).End(xlUp)).Copy
 Sheet10.Range("A1048576").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
 Sheet10.Range("A2", Sheet10.Range("A" & Rows.Count).End(xlUp)).RemoveDuplicates 1
End Sub
Private Sub Worksheet_Activate()
 Sheet2.Range("A2", Sheet2.Range("A" & Rows.Count).End(xlUp)).Copy
 Sheet10.Range("A1048576").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
 Sheet10.Range("C2", Sheet10.Range("A" & Rows.Count).End(xlUp)).RemoveDuplicates 1
End Sub

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.

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

Sheet1 B to Sheet10 A
Sheet1 D to Sheet10 B
Sheet2 B to Sheet10 D
Sheet2 G to Sheet10 E
Sheet3 E to Sheet10 H
Sheet3 J to Sheet10 G
Sheet4 C to Sheet10 J
Sheet5 E to Sheet10 M
Sheet5 J to Sheet10 L
Sheet6 B to Sheet10 O
Sheet6 G to Sheet10 P
Sheet7 E to Sheet10 S
Sheet7 J to Sheet10 R
Sheet8 B to Sheet10 U
Sheet8 G to Sheet10 V

Quote from Carim

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

Oh dear. That's odd I didn't remembering doing such a thing. Lets fix that!

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!

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.