+ Reply to Thread
Results 1 to 3 of 3

Code to Create New Sheets Based on Cell Value, and Copy All matching Rows to New Sheet

  1. #1
    Registered User
    Join Date
    11-01-2012
    Location
    Central PA
    MS-Off Ver
    Excel 2007
    Posts
    2

    Code to Create New Sheets Based on Cell Value, and Copy All matching Rows to New Sheet

    Hello! I am very new to this and I have found some code that is helpful, but I do not know how to combine the two efficiently, or even if the second code is exactly what I need.

    I have a purchasing log where every PO for a department is input. Based on the Vendor Name in Column F on the main sheet "Entered", I need a macro that will create a new page for each different Vendor and name it the Vendor Name, copy the Header in Row 1 to the new sheet, and then also copy all rows with the same Vendor name to its corresponding sheet. I also need it to cut and rerun each time or only search for new inputs to add to the sheets, always leaving the "Entered" sheet untouched, because the copy/paste code I have now duplicates onto the Vendor sheets every time I run it so the list keeps repeating.

    I attached a sample of the spreadsheet. The code I have started are SortPOList and SortPOsByVendor.

    SortPOList is the one that is creating the new pages - it starts to run, but once it runs into a duplicate Vendor name it shows that this is a duplicate and cannot create a second sheet for this vendor. I need it to ignore duplicates and move to the next new one. How can I do this?

    SortPOsByVendor is the code I originally created to copy/paste rows to the new vendor pages. With this code, I actually had a page for each vendor. But as the log is being continually updated, if I don't catch a new vendor I will not therefore have a page for it and will also need to continuously update the macro. Is there a way to make a simpler code that will copy all rows with the same Vendor to the new sheets that were created?

    Thank you in advance for your help - I really appreciate it! This has been driving me nuts!!
    Attached Files Attached Files

  2. #2
    Forum Contributor BeachRock's Avatar
    Join Date
    11-01-2011
    Location
    Oregon, United States
    MS-Off Ver
    Excel 2016
    Posts
    403

    Re: Code to Create New Sheets Based on Cell Value, and Copy All matching Rows to New Sheet

    Hi CGL,

    I have code just for this that works great. With some slight modifications specific to your requirements, it should do what you need.

    Have a look:

    Please Login or Register  to view this content.
    It does check if the proposed sheet name already exists but it only uses one specific cell as a reference for what to name the new sheet. Other than that small hurdle of having it work from the entire range of your list of vendors, you should be able to replace names of worksheets and ranges and then duplicate it for each specific vendor in your column list. I'm sorry though, I'm not a good VBA programmer so I don't know how to get this to reference the entire list of vendors instead of just the specific cell.... I hope it helps.

    FYI - the code at the end hides the newly created sheet and you will probably want to remove that part.
    -------------
    Tony

  3. #3
    Registered User
    Join Date
    11-01-2012
    Location
    Central PA
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: Code to Create New Sheets Based on Cell Value, and Copy All matching Rows to New Sheet

    Thanks so much! I was too busy to try this on Friday, but I am going to try today and let you know

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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