Excel VBA automatically copy entire row to different sheet based on cell value

  • Hi... My excel have multiple sheets as below:

    • Sheet1(Master Data) ... No, Name, Div, Dept, Status
    • Sheet2(High)
    • Sheet3(Medium)
    • Sheet4(Low)

    I need to write a VBA code that will COPY an entire row automatically into a respective sheet if the value in the "Status" cell meet the criteria "High", "Medium" or "Low".


    Whenever changes was made in the Master Data, it is also reflect to the respective sheets automatically.


    Hope someone can help and thanks in advanced.

  • Hi,


    For any contributor willing to give a hand ...attaching a sample file will be more than appreciated ... :wink.

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Hello,


    Thanks for your test file.


    Basically, you need to explode your database into individual sheets ... :wink:


    Attached is your test file ...


    Hope this will help

    Files

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • The data does not include a Column for Status.


    I wouldn't recommend split data Here's some code that can be adapted for what you need




  • Wowww .... fantastic....


    1) What if the header is "A1:D3"


    2) What if i wanna it to be likes check/sync data every 5 mins or detect any changes/new data being entered.


    Thank you so much for your assistance...

  • The data does not include a Column for Status.


    I wouldn't recommend split data Here's some code that can be adapted for what you need




    Greattttt .... Where should i paste the coding...



  • Glad you like the macro ...!!! :wink:


    1. A Header over 3 rows does not make sense ... one row is enough even if you need to triple the row height ...


    2. A worksheet change event can run your macro each time you are modifying it ...


    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Hello again,


    Forgot to mention ... if, instead of running you macro whenever a change is made, you do need a periodic update every 5 minutes ... the instruction Application.OnTime can handle this for you ...:wink:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)