Posts by PuntingJawa

    I thought you meant to go to another Forum.


    Neverless you should explain what you want in this question.

    Very well. Essentially what took me so long was adding both databases into one workbook as to make logging easier as well as keeping the format as close to the same per worksheet. IE not needing to open two workbooks to accomplish the same thing I could do with 1 workbook. With the first workbook I currently have utilized from the above mentioned forum threads VB code to auto log when the sheet is selected. Which works amazing and I highly recommend using it to anyone who has a similar situation they're trying to work with. Don't forget to give thanks to the ones who helped.

    What I am wondering is if there is a way to do the same thing as the auto logging in the first file but have it save to the second file?

    So from here Test full DB (Macro Enabled).xlsm to here SN Log 2021.xlsx instead of having it do it in the same worksheet as you can see in the first file.

    You really expect is to go to another Forum to read your question?

    It's this forum and you were the one that expressed that I should make a new post with this specific question. If you would like I can copy and paste every detail here in sequence. I was unsure if I should considering the progress that was made in that other post and I wanted to give credit to those who helped me. There's no need to be rude or condescending.

    Other forum post with lots of information


    If you view the above link you'll see what I have been working on. Which, by the way works amazingly for what I was a concept that I was messing with to a realization which I am grateful for. Which leads me to a new idea that I want to try and am curious if it is possible. On to the task at hand.

    I currently have two excel databases that change based on my criteria. It's actually a serial number generator with each part having its own sheet and a log on the last sheet that the VBA is capturing/deleting duplicates (See above thread). What I want to do is combine these two databases into one workbook but instead of having the log in the same workbook I'd like to have it in another in real time. Would this be possible?

    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.

    Thanks for sharing. In future please post links to any other Forums the question is asked in when opening the question.


    Note, that using ROW() in a serial number is not a good idea because the number will be fixed to that row. For example, you cannot sort data alongside it .

    The ROW part worked somewhat but wasn't exactly what I needed in this instance due to the nature of my workbook. So you are correct.

    I started this project to be a database for a label printer/finisher. We use NiceLabel software which needed me to keep the Excel sheet in a certain format to work. When I started I was entering all information manually for the S/N's as well as other information that was being printed.

    Also my apologies for not linking the other forum I had this question in (That thread was started to help with my auto generating part and just took off). I was unaware it was OK to do so and didn't out of respect for both forums. I'll do that now. In this thread on the board you can see the progress of my workbook. With yours and their collective help I have managed to really bring this serial number generator to a whole new level that I never thought could be possible. Furthermore, I truly appreciate you all. I never could have done this without the collective knowledge you all have bestowed upon me. Adding link to my other thread at the bottom.

    x-amount-of-serial-numbers-based-on-criteria

    For even rows with auto sequence.

    Code
    =INDEX(B2:B12,SEQUENCE(ROUNDUP(P2/2,0),,,2))

    For odd rows with auto sequence,

    Code
    =INDEX(B2:B12,SEQUENCE(P2/2,,2,2))

    I received help on this in another thread I had elsewhere. Although the =mod works, and rather well, it wasn't exactly what I was looking for. These two were the ones that worked best given the nature of my Excel workbook. I'm posting these here in case someone else needs something similar. I'll also add a sample file at the bottom so you can see what I was trying to accomplish.

    Example test.xlsx

    Example:

    Say I want the following cells to be captured up to 999 but every other one.

    B2,B4,B6 ect to G2, G3, G4 and

    B3,B5,B7 ect to H2,H3,H4 and so on.


    Is there a formula that can do this? I use the following formulas to generate serial numbers as seen in the attached. So far I have manually set the above mentioned locations but was hoping to do this with a formula instead similar to how my sequence formula works.

    Code
    =L11&TEXT(SEQUENCE(P2),"")
    =L2&M2&N2&TEXT(SEQUENCE(P2)+O2-1,"000")
    =L8&TEXT(SEQUENCE(P2),"")

    Example test.xlsx

    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!

    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.

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

    So by that do you mean this.

    Code
    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

    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.

    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
    Sheet10.Range("A1048576").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues

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

    Code
    Option Explicit

    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
    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?

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