+ Reply to Thread
Results 1 to 12 of 12

macro if data in column a = set word to insert that column into sheet 2 or 3 based on word

  1. #1
    Registered User
    Join Date
    03-13-2014
    Location
    Green Bay, WI
    MS-Off Ver
    Excel 2007
    Posts
    13

    macro if data in column a = set word to insert that column into sheet 2 or 3 based on word

    I am very inexperienced at recording macros but I would like to setup a macro that if data in column A equals a particular word I would like to insert the whole row onto a corresponding sheet. For example column A is a list of departments, column B is a list of projects for that department and remaining columns are for tracking purposes of that project. The other sheets are broken into departments. I already have a list of 15 projects set-up to correspond to their correct sheets, but if additional columns are added, I would like it to automatically update and add that column to the correct sheet. I need separate sheets in order to quickly glance and possibly print for each department without having other departments projects listed on it.

    Thank you for any and all help!

  2. #2
    Forum Expert Solus Rankin's Avatar
    Join Date
    05-24-2013
    Location
    Hollywood, CA
    MS-Off Ver
    Win7 Office 2010 VS Express 2012
    Posts
    2,655

    Re: macro if data in column a = set word to insert that column into sheet 2 or 3 based on

    Can you redact any sensitive information from the spreadsheet and attach it as an example? Maybe with a before and after example?

    This will give us a better picture of how it is laid out.

    To attach an example workbook, click the 'Go Advanced' button below and use the paperclip in the toolbar to navigate to your file.
    Thanks,
    Solus


    Please remember the following:

    1. Use [code] code tags [/code]. It keeps posts clean, easy-to-read, and maintains VBA formatting.
    Highlight the code in your post and press the # button in the toolbar.
    2. Show appreciation to those who have helped you by clicking below their posts.
    3. If you are happy with a solution to your problem, mark the thread as [SOLVED] using the tools at the top.

    "Slow is smooth, smooth is fast."

  3. #3
    Registered User
    Join Date
    03-13-2014
    Location
    Green Bay, WI
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: macro if data in column a = set word to insert that column into sheet 2 or 3 based on

    Attached is an example of the document.

    Thank you for your quick response!

    Projects Notes Macro Test.xlsm

  4. #4
    Forum Expert Solus Rankin's Avatar
    Join Date
    05-24-2013
    Location
    Hollywood, CA
    MS-Off Ver
    Win7 Office 2010 VS Express 2012
    Posts
    2,655

    Re: macro if data in column a = set word to insert that column into sheet 2 or 3 based on

    Try placing this in the sheet module for sheet CRH Projects
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    03-13-2014
    Location
    Green Bay, WI
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: macro if data in column a = set word to insert that column into sheet 2 or 3 based on

    I get the following error when I insert a row:
    Run-time error '9':
    Subscript out of range
    My options are to End / Debug / Help.

  6. #6
    Forum Expert Solus Rankin's Avatar
    Join Date
    05-24-2013
    Location
    Hollywood, CA
    MS-Off Ver
    Win7 Office 2010 VS Express 2012
    Posts
    2,655

    Re: macro if data in column a = set word to insert that column into sheet 2 or 3 based on

    My mistake. Try this:
    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    03-13-2014
    Location
    Green Bay, WI
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: macro if data in column a = set word to insert that column into sheet 2 or 3 based on

    No problem. I am very grateful for your help!

    It allows me add rows to Sheet CRH Projects, but it does not add that row to the corresponding sheet based on the department.

  8. #8
    Forum Expert Solus Rankin's Avatar
    Join Date
    05-24-2013
    Location
    Hollywood, CA
    MS-Off Ver
    Win7 Office 2010 VS Express 2012
    Posts
    2,655

    Re: macro if data in column a = set word to insert that column into sheet 2 or 3 based on

    Okay, I think I misunderstood your request. Are the updates of the cells themselves going to happen through formulas?

    Basically what you want, if you add a line (insert a line) to the summary sheet it will automatically add the line to the bottom of the individual team sheet?

  9. #9
    Registered User
    Join Date
    03-13-2014
    Location
    Green Bay, WI
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: macro if data in column a = set word to insert that column into sheet 2 or 3 based on

    I apologize, I probly didn't explain it correctly.

    Yes, if I add a line (insert a line) to the summary sheet I want it to automatically add the line to the bottom of the individual team sheet.

    I also want data entered into those cells on the summary sheet (most likely at a later time) for columns D-J to have formulas in them to automatically update on the individual team sheet. Can this be written or does this need to be done manually?

  10. #10
    Forum Expert Solus Rankin's Avatar
    Join Date
    05-24-2013
    Location
    Hollywood, CA
    MS-Off Ver
    Win7 Office 2010 VS Express 2012
    Posts
    2,655

    Re: macro if data in column a = set word to insert that column into sheet 2 or 3 based on

    Maybe this is what you're looking for
    Please Login or Register  to view this content.

  11. #11
    Registered User
    Join Date
    03-13-2014
    Location
    Green Bay, WI
    MS-Off Ver
    Excel 2007
    Posts
    13

    Smile Re: macro if data in column a = set word to insert that column into sheet 2 or 3 based on

    This works PERFECT!!!!!!! THANK YOU!!!!!!!

  12. #12
    Registered User
    Join Date
    03-13-2014
    Location
    Green Bay, WI
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: macro if data in column a = set word to insert that column into sheet 2 or 3 based on

    This works great for adding, but this document is more or less a department to-do list so items are being added and removed. When items are being removed from the main page, the corresponding page then has #REF! where the old data was. Is there a way for it to just delete the data from the corresponding page? Also when data is being replaced by a new task item, the new item goes to the correct page, but it also remains on the previous individual page. Is there a way to remove the data from the corresponding page when the department name is changed?

+ 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. Macro to insert a column and fill that column with word "January"
    By aka189 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-23-2012, 09:22 PM
  2. [SOLVED] Moving a row from a main sheet based on a word in a column
    By Greggatm in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 09-24-2012, 05:53 PM
  3. Find word in Column-cut row-insert into new sheet
    By Vrlac8 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-23-2012, 03:28 PM
  4. Macro to copy a row of data to a different sheet based on a word in a column
    By trav96817 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-22-2012, 09:51 PM
  5. Adding Brackets Around A Word Based On Word In Another Column
    By jessekanclerz in forum Excel General
    Replies: 1
    Last Post: 11-29-2010, 10:45 PM

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