+ Reply to Thread
Results 1 to 8 of 8

Copy Row to new worksheet IF cell contains specific text

  1. 03-06-2021, 05:01 AM #1
    aussie.lilly
    aussie.lilly is offline
    Registered User
    Join Date
    03-06-2021
    Location
    Australia
    MS-Off Ver
    2016
    Posts
    9

    Copy Row to new worksheet IF cell contains specific text

    Hi,

    I am after a solution to automatically copy an entire row to a new worksheet based on meeting a certain condition.
    For example on the workbook attached
    - First Sheet is Summary of all employees
    - Second Sheet is casual, third is part time and fourth is full time.

    What i want to happen is this - When a new employee is added to Sheet 1 (summary), they automatically get placed in the appropriate worksheet based on their employment status. Not just a single cell, but ALL their information from the entire row. So for example, when I open the casual sheet, I want to see a list of all casual employees only including their DOB and position.

    I have tried pivot tables but don't like how it presents the data

    Thanks in advance
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 03-06-2021, 05:04 AM #2
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,719

    Re: Copy Row to new worksheet IF cell contains specific text

    Welcome to the forum.

    You could use PowerQuery to get the data into the format you want. There is a link in my signature line to information about PQ. If you had Office 365, you'd be able to use the new FILTER function.

    Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

    It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

    - Follow this link to learn how to install Power Query in Excel 2010 / 2013.

    - Follow this link for an introduction to Power Query functionality.
    Ali

    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
  3. 03-06-2021, 06:23 AM #3
    aussie.lilly
    aussie.lilly is offline
    Registered User
    Join Date
    03-06-2021
    Location
    Australia
    MS-Off Ver
    2016
    Posts
    9

    Re: Copy Row to new worksheet IF cell contains specific text

    Thank-you for your fast response! That has worked perfectly. Now the next thing is to get bosses to remember to save and refresh the table whenever they add a new staff member.
    Reply With QuoteRegister To Reply
  4. 03-06-2021, 06:32 AM #4
    Glenn Kennedy
    Glenn Kennedy is online now
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,018

    Re: Copy Row to new worksheet IF cell contains specific text

    Personally, i'd use a formula... It automatically updates.


    =IFERROR(INDEX('All Staff'!A:A,AGGREGATE(15,6,ROW('All Staff'!$A$2:$A$20)/('All Staff'!$D$2:$D$20=$A$1),ROWS(A$4:A4))),"")

    in A4, copied across and down. Adjust the ranges in red to suit.

    see sheet.
    Attached Files Attached Files
    Glenn



    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
    Reply With QuoteRegister To Reply
  5. 03-06-2021, 06:39 AM #5
    aussie.lilly
    aussie.lilly is offline
    Registered User
    Join Date
    03-06-2021
    Location
    Australia
    MS-Off Ver
    2016
    Posts
    9

    Re: Copy Row to new worksheet IF cell contains specific text

    Thats exactly what I was looking for!
    However the naive part of me cannot interpret it to adjust it to my real worksheet.
    Which parts of the formula would I need to adjust to change for e.g. parttime and permanent?
    Reply With QuoteRegister To Reply
  6. 03-06-2021, 06:45 AM #6
    Glenn Kennedy
    Glenn Kennedy is online now
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,018

    Re: Copy Row to new worksheet IF cell contains specific text

    You have two choices. Either enter their status in a cell reference (as I did in A1 on the Casual sheet)

    =IFERROR(INDEX('All Staff'!A:A,AGGREGATE(15,6,ROW('All Staff'!$A$2:$A$20)/('All Staff'!$D$2:$D$20=$A$1),ROWS(A$4:A4))),"")

    or replace the bit in red like this:

    =IFERROR(INDEX('All Staff'!A:A,AGGREGATE(15,6,ROW('All Staff'!$A$2:$A$20)/('All Staff'!$D$2:$D$20="Casual"),ROWS(A$4:A4))),"")
    Reply With QuoteRegister To Reply
  7. 03-06-2021, 06:48 AM #7
    aussie.lilly
    aussie.lilly is offline
    Registered User
    Join Date
    03-06-2021
    Location
    Australia
    MS-Off Ver
    2016
    Posts
    9

    Re: Copy Row to new worksheet IF cell contains specific text

    Quote Originally Posted by Glenn Kennedy View Post
    You have two choices. Either enter their status in a cell reference (as I did in A1 on the Casual sheet)

    =IFERROR(INDEX('All Staff'!A:A,AGGREGATE(15,6,ROW('All Staff'!$A$2:$A$20)/('All Staff'!$D$2:$D$20=$A$1),ROWS(A$4:A4))),"")

    or replace the bit in red like this:

    =IFERROR(INDEX('All Staff'!A:A,AGGREGATE(15,6,ROW('All Staff'!$A$2:$A$20)/('All Staff'!$D$2:$D$20="Casual"),ROWS(A$4:A4))),"")
    Perfect now I see it. Thank you so much!
    Reply With QuoteRegister To Reply
  8. 03-06-2021, 06:51 AM #8
    Glenn Kennedy
    Glenn Kennedy is online now
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,018

    Re: Copy Row to new worksheet IF cell contains specific text

    You're welcome.


    It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

    Finally, if that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Find specific text within workbook's name, copy and paste it to specific cell
    By axtryo in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 01-09-2017, 02:57 AM
  2. [SOLVED] Enter data into a userform text box to show in a specific cell in a specific worksheet
    By Stevecraig211 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-10-2016, 10:02 AM
  3. [SOLVED] VBA to Find Specific Text, then Copy range to next open cell under specific text
    By Remotruker in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-24-2015, 10:59 AM
  4. [SOLVED] Copy worksheet with specific name and no blank cell in a specific range
    By graiggoriz in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 11-05-2013, 11:50 AM
  5. Find specific text in a cell within a worksheet and copy the entire row to another sheet
    By brgr4u in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-31-2013, 03:41 PM
  6. Macro to copy specific line from text file and paste into specific cell in excel
    By keeneye in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-05-2013, 10:35 AM
  7. Find cell with specific text, copy and paste cell with data to another worksheet, pls help
    By arifmasum in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-06-2012, 06:44 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1