Posts by cwest530

    Re: Increment lot numbers as products are scanned into excel


    Thanks for the help, but I figured out my problem before you answered.

    The problem I am having with my code is instead of my lot number being "1000-1" then allowing me to scan another product into excel, the code keeps looping all the way from "1000-1" to "1000-1000" I want it to look like this:
    MRN Lot number
    1234-1 1000-1
    1234-2 1000-2
    1234-3 1000-3
    instead it just loops in the active cell without moving down the range. Anyone have any ideas or suggestions how to make this work. Here is what I have:


    I have tried numerous ways to get my code to allow ms access to be open while a linked table sheet from excel is open, however it always ends up saving a copy of the said excel file instead of just updating the linked table in excel. The scenario is that at my company we need to be able to have several people be able to view the ms access database where excel data is being stored. While also the linked tables being open and able to continue to save data normally without being deterred by access. I am thinking that I could possibly use some sort of If function to save on a time interval, for example "if activeworkbook saves then do nothing else save again in 60 secs" and loop until it saves. I am not really sure what the best course of action is for this, any tips or suggestions would be greatly appreciated. If clarification is needed just let me know. This is what I have so far:


    Re: Workbook to workbook data transfers


    pike that gets a compile error "expected =", however I was able to figure out how to make a piece of code work that could transfer data from one open workbook to a closed workbook, the problem I am facing now is inserting values with each data entry. I am trying to get this piece of code to copy the data to a workbook then add a value to the next column called "film" and then date and time. This is what I have:



    Any suggestions would be most appreciated.

    Right now I am trying to figure out how to transfer data from one workbook to another, the problem I am dealing with is, getting the data to go the next available row, later I also plan on inserting some code to help with duplicates. However for now I am just trying to get the code from one excel workbook to open another workbook copy the data in A1 and B1 then transfer it to another workbook called "WB2WBtest2.xlsm" in the next available row. Here is what I have so far any tips and or suggestions would be greatly appreciated.


    Re: Selecting ten cells and pasting them to new sheet horizontally in i loop


    Thanks that worked, I needed a piece of code that spat out lines of ten and went downwards because the way another program reads excel files for printing labels for shipping. This code will generate a list of numbers in increments of ten, ten numbers = ten labels = one lot. Thus automating operations, for one of my coworkers. Making everything more efficient was the business scenario.

    I am trying to get a loop function to select ten cells in column C and then paste those selected columns to sheet 4 and paste them horizontally, the problem I am encountering is instead of copying the ten cells in one column it selects one cell in the column and copy's it ten times in sheet 4. I realize this is because of how my code is set, my question is how can I make vba select the the ten cells then paste it on the other sheet and then have vba select the next ten cells in the column? This is what I have:




    Any tips and or suggestions would be most appreciated, if it is not clear just let me know and I can try to rephrase my question. Thanks

    Re: If duplicate value found then MsgBox & remove duplicate


    Sorry about that I have been going back between access and excel, making a sample interface. By record I just meant row, but anyways I uploaded the workbook, check sheet 2 as sheet 1 will always be empty as you will see its only purpose is to input data then copy and paste to sheet 2 which is linked to access. If it seems confusing just let me know and I can try to better explain. But I am trying to get the goDupe Sub to check for duplicates in sheet 2 and then if there is a duplicate prompt a message box that says "duplicate entry" and then remove the duplicate. The help is most appreciated. The unique values are contained in column A, all other columns may have duplicate values. The help and or suggestions is most appreciated.

    Re: If duplicate value found then MsgBox & remove duplicate


    What I got so far is this:





    Code
    Sub GoDupe()
    Cells.RemoveDuplicates Columns:=Array(1), Header:=xlNo
    Range("A65536").End(xlUp).Offset(1, 0).Select
    End Sub


    My code removes duplicates no problem, however I want it to prompt a msgbox if there is a duplicate within the table, if there is no duplicate I want it to do nothing.


    Moderator Comment: Please reread the rules you agreed to when you joined the forum and use code tags when ever posting VBA in a thread.

    I am trying to write a piece of code that when a new record is entered it will prompt a duplicate scan, and if a duplicate is found execute a msgbox that says something like " Error" & value of duplicate " is a duplicate value please enter a valid entry" But only if there is a duplicate entry entered into the excel table. I have looked around and found a lot of code similar to this but nothing that really seems to be executing exactly what I want to happen. any help or suggestions would be most appreciated.