+ Reply to Thread
Results 1 to 8 of 8

Create and/or update sheets based on value in column A

  1. #1
    Registered User
    Join Date
    08-01-2012
    Location
    Norway
    MS-Off Ver
    Excel 2010
    Posts
    20

    Create and/or update sheets based on value in column A

    Hi all

    My second challenge...

    I have a workbbok that contains a master sheet (Import fra Mamut). This is a result of a CSV-import on regular intervals and contains the following information:
    - Column A = Vendor Number
    - Column B = Vendor Name
    - Column C = Vendor Item No
    - Column D = Item No
    - Column E = Item Name

    The number of rows will change as I add or remove items from my database.

    What I would like is to loop thru Column A and create a new worksheet with a name that matches the vendor no, i.e. value in column A is "50002" which would create a sheet called "RS Components".
    However, if the sheet already exist it should obviosly not create a new one. It would be nice if the sheets where sorted alphabetically.

    Once the sheet is created it should copy all rows with value "50002" in column A to the newly created sheet, or update if it's already existing.

    I'm going to use this for calculating prices and stuff, and therefore I can't delete everything on the sheet (if it exist). Or, if possible, also create a routine that copies the formula to the newly added number of rows.

    Range A:E will need to be updated from the master sheet to the newly created or updated sheets (column B is not really neccesary to be honest. I will start at column G with my calculations, 5-6 for each row.

    Attached is a sample workbook that might show what I'm looking for.

    Any help much appriciated.

    Best regards
    Lars-Inge
    Attached Files Attached Files

  2. #2
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,519

    Re: Create and/or update sheets based on value in column A

    Give this a try,

    Sample-v1.xlsm

  3. #3
    Registered User
    Join Date
    08-01-2012
    Location
    Norway
    MS-Off Ver
    Excel 2010
    Posts
    20

    Re: Create and/or update sheets based on value in column A

    Hi

    It works, but it does need some modification I think.

    First; when I run it I get a type mismatch on the privat function:
    Please Login or Register  to view this content.
    If I change it and remove (comment out) the the first "IF" statement and only use the last, it works.

    That said, every time it runs, it adds rows to the already existing sheets. I guess I can modify the code to select the cells and remove them before the code is ran?

    I'm running Office 2010, by the way.

    Br
    Lars-Inge

  4. #4
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,519

    Re: Create and/or update sheets based on value in column A

    I assumed you were wanting to add to the bottom of the sheet if sheet already in workbook.
    Once the sheet is created it should copy all rows with value "50002" in column A to the newly created sheet, or update if it's already existing
    Try this

    Sample-v2.xlsm

  5. #5
    Registered User
    Join Date
    08-01-2012
    Location
    Norway
    MS-Off Ver
    Excel 2010
    Posts
    20

    Re: Create and/or update sheets based on value in column A

    Excellent, that did the trick, thank you very much.

  6. #6
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,519

    Re: Create and/or update sheets based on value in column A

    @ClassicVAG

    Also remember to mark your Posts on the form as Solved, if your questions have been answered.

  7. #7
    Registered User
    Join Date
    08-01-2012
    Location
    Norway
    MS-Off Ver
    Excel 2010
    Posts
    20

    Re: Create and/or update sheets based on value in column A

    I will do, sorry about that.

  8. #8
    Registered User
    Join Date
    08-01-2012
    Location
    Norway
    MS-Off Ver
    Excel 2010
    Posts
    20

    Re: Create and/or update sheets based on value in column A

    I have done some changes to my file (the software I'm exporting from has changed and I'm using a different report to get the date), and the spreadsheet isn't working as supposed anymore

    It does create sheets and update them, all the way til the last sheet where it generates an error on the code (second "block" of code) marked below.

    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    I have tried figuring out this by myself, but I'm not able to.

    Anyone that can help?

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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