+ Reply to Thread
Results 1 to 15 of 15

VBA for adding new stock to old spreadsheet without repeating, then highlighting said cell

  1. #1
    Registered User
    Join Date
    10-02-2013
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    7

    VBA for adding new stock to old spreadsheet without repeating, then highlighting said cell

    Hi there, I'm trying to several thousand new stock items from the manufacturer sheet to an old stock sheet without repeating stock that's already in the old spreadsheet, and once the new row (stock item) has been added to the old sheet, the Part no. cell then needs highlighting (preferably green) to show that it's new.

    Our spreadsheets are laid out A = Part no. B = Price. C = description.
    D = RRP, E = EAN NO. F = Weight (KG)

    Hopefully I made sense of that, any help is appreciated, thankyou.

  2. #2
    Forum Expert OllieB's Avatar
    Join Date
    12-20-2012
    Location
    Netherlands
    MS-Off Ver
    Excel 2007 (home) & 2010 (office)
    Posts
    1,542

    Re: VBA for adding new stock to old spreadsheet without repeating, then highlighting said

    Would you be able to post examples of the two worksheets involved (just a few rows to get a feeling of the layout and data)?
    If you like my contribution click the star icon!

  3. #3
    Registered User
    Join Date
    10-02-2013
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: VBA for adding new stock to old spreadsheet without repeating, then highlighting said

    Hi OllieB, thankyou for helping me, here's the old and new stock sheets, the old one is currently highlighted to show discontinued stock or price updates.

    2.png
    1.png

  4. #4
    Forum Expert OllieB's Avatar
    Join Date
    12-20-2012
    Location
    Netherlands
    MS-Off Ver
    Excel 2007 (home) & 2010 (office)
    Posts
    1,542

    Re: VBA for adding new stock to old spreadsheet without repeating, then highlighting said

    I am sorry but I would really like to have copies of the workbooks involved.

    Can you make a copy of both, remove most of the data data from the copies (so they are not too big) and post them?

  5. #5
    Registered User
    Join Date
    10-02-2013
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: VBA for adding new stock to old spreadsheet without repeating, then highlighting said

    Quote Originally Posted by OllieB View Post
    I am sorry but I would really like to have copies of the workbooks involved.

    Can you make a copy of both, remove most of the data data from the copies (so they are not too big) and post them?
    OllieB, not a problem:
    http://filesmelt.com/dl/Desktop301.rar

    Thankyou again.

  6. #6
    Forum Expert OllieB's Avatar
    Join Date
    12-20-2012
    Location
    Netherlands
    MS-Off Ver
    Excel 2007 (home) & 2010 (office)
    Posts
    1,542

    Re: VBA for adding new stock to old spreadsheet without repeating, then highlighting said

    I am afraid it is still not clear to (despite now having the workbooks).

    I am guessing that the old one is text1.xls and the new one text2.xls, and you want to import the details of text2 into text1. Right?

    Where it gets vague is that text1.xls does not contain any column headers and the format of the data contained in text1 does not look anything like the data in text2, and moreover text2 contains more columns than text1.

    Can you either provide a copy of text1 with headers to I can match the columns and just tell me what the mapping should be like.

  7. #7
    Registered User
    Join Date
    10-02-2013
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: VBA for adding new stock to old spreadsheet without repeating, then highlighting said

    Ollie, I'm only interested in adding Part no, price and description, the first three columns, I need to add the new stock from text2 to text1, but only the stock that doesn't already exist on text1. Then I need it to highlight one of the cells in the new row, preferably Part No. and preferably green. Are the column headers important in context?
    Last edited by eltocliousus; 10-02-2013 at 09:18 AM.

  8. #8
    Forum Expert OllieB's Avatar
    Join Date
    12-20-2012
    Location
    Netherlands
    MS-Off Ver
    Excel 2007 (home) & 2010 (office)
    Posts
    1,542

    Re: VBA for adding new stock to old spreadsheet without repeating, then highlighting said

    Please try the attached workbook. Because I am not sure what you are doing with the existing formatting, I have not touched that, I only add a green background for the rows that are added
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    10-02-2013
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: VBA for adding new stock to old spreadsheet without repeating, then highlighting said

    Ollie, the one you sent just has the highlights that I've added, I don't see any green. I ran the couple of Macro's in the workbook however none of them prompted me to open the other spreadsheet with the new information in, so it didn't work.

    The existing format is just A = Part No. B = Price and C = Description, that's the only information we need the rest in the new spreadsheet isn't necessary for now, although if you want to add them to be automatically copied to the new spreadsheet I can edit the original with similar columns.
    Last edited by eltocliousus; 10-02-2013 at 09:50 AM.

  10. #10
    Forum Expert OllieB's Avatar
    Join Date
    12-20-2012
    Location
    Netherlands
    MS-Off Ver
    Excel 2007 (home) & 2010 (office)
    Posts
    1,542

    Re: VBA for adding new stock to old spreadsheet without repeating, then highlighting said

    The only macro you will need to run is the macro titled "updateNewStock" in the module named "Module1".

    When you read the code provided you will see that the macro assumes that the workbook to be imported (test2) is stored in the same directory as the master workbook (test1) and is named "test2".

    Hence, you are not prompted.

    I have tested the code and it works, as long as you run the right macro :-)

  11. #11
    Registered User
    Join Date
    10-02-2013
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: VBA for adding new stock to old spreadsheet without repeating, then highlighting said

    OllieB,
    Legend,

    It works, you were right I didn't have them both up at the same time, shows how new I am to this.
    Would I be able to post back here if necessary tomorrow or at a future date in the near future, or would I be better off making a new thread?

    Again thanks Ollie, enjoy your day.

  12. #12
    Forum Expert OllieB's Avatar
    Join Date
    12-20-2012
    Location
    Netherlands
    MS-Off Ver
    Excel 2007 (home) & 2010 (office)
    Posts
    1,542

    Re: VBA for adding new stock to old spreadsheet without repeating, then highlighting said

    If you have problems with the solution offered, or a small change in the requirement you can post here. For new requirements it is better to create a new thread

  13. #13
    Registered User
    Join Date
    10-02-2013
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: VBA for adding new stock to old spreadsheet without repeating, then highlighting said

    Quote Originally Posted by OllieB View Post
    If you have problems with the solution offered, or a small change in the requirement you can post here. For new requirements it is better to create a new thread
    Ollie,

    I have this code I'm using for updating old spreadsheets with new ones, adding or editing stock in the old spreadsheet and then highlighting the item edited to show it's been edited.

    Please Login or Register  to view this content.
    It checks the old list for the entire Part No. If the part number is found in the new spreadsheet it edits the second column of the old one (Price on both sheets) to new price on the new sheet and then highlights the price to show it's been edited. If it can't find the part number it highlights it in the old document to show it needs to be deleted, then we can batch the information and upload it straight to the database.

    Is it possible to add a second part to the code that, if the item exists it edits a third column and again highlights it.

    Cheers!

  14. #14
    Forum Expert OllieB's Avatar
    Join Date
    12-20-2012
    Location
    Netherlands
    MS-Off Ver
    Excel 2007 (home) & 2010 (office)
    Posts
    1,542

    Re: VBA for adding new stock to old spreadsheet without repeating, then highlighting said

    sure, will provide the updated code tomorrow morning

  15. #15
    Forum Expert OllieB's Avatar
    Join Date
    12-20-2012
    Location
    Netherlands
    MS-Off Ver
    Excel 2007 (home) & 2010 (office)
    Posts
    1,542

    Re: VBA for adding new stock to old spreadsheet without repeating, then highlighting said

    as requested
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. VBA Code to pull Stock Balance Sheet Data from a Stock Symbol in a cell
    By akash1229 in forum Excel Programming / VBA / Macros
    Replies: 22
    Last Post: 02-27-2015, 04:37 PM
  2. Replies: 4
    Last Post: 01-29-2013, 02:26 AM
  3. Replies: 0
    Last Post: 10-28-2012, 07:22 AM
  4. Highlighting cells when stock is low
    By becky object in forum Excel General
    Replies: 9
    Last Post: 03-28-2011, 07:44 AM
  5. Replies: 10
    Last Post: 08-26-2010, 04:23 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1