+ Reply to Thread
Results 1 to 14 of 14

How to copy row to another sheet based on cell value - Macro or Formula

  1. #1
    Registered User
    Join Date
    07-15-2019
    Location
    North West
    MS-Off Ver
    Excel 2010
    Posts
    10

    Post How to copy row to another sheet based on cell value - Macro or Formula

    I have a workbook where there is a main input sheet (Main Order List), currently I am copying all data over to various other sheets (Different Dept, Sites etc.) within the work book which is becoming a more tedious task.

    Therefore, I am checking if there is any way of entering a short key within the Main Order List that will automatically copy the row into the relevant sheet, i.e. 'Office' will copy to Sheet titled Office, 'Contract' will copy to Sheet titled Contract etc. so I am only inputting on the main sheet and the relevant data is copying over automatically to the correct sheets.

    Apologies if this has already been asked - I have tried finding an example Macro / formula to work with but no luck so far!

  2. #2
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,581

    Re: How to copy row to another sheet based on cell value - Macro or Formula

    It would be easier to help if you could attach a copy of your file (de-sensitized if necessary).
    You can say "THANK YOU" for help received by clicking the Star symbol at the bottom left of the helper's post.
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  3. #3
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    20,899

    Re: How to copy row to another sheet based on cell value - Macro or Formula

    I have done this several times in the past using formulae, and to avoid having one sheet each for your categories, you can have a drop-down in that sheet to select Office, Contract etc. and then the appropriate data will populate.

    It would help if you attached a sample Excel workbook.

    To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

    Don't try to use the Paperclip icon, as it doesn't work on this forum.

    Hope this helps.

    Pete

  4. #4
    Registered User
    Join Date
    07-15-2019
    Location
    North West
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: How to copy row to another sheet based on cell value - Macro or Formula

    Thanks for the quick replies - I have quickly done a test example spreadsheet to help show what I'm trying to do as the data I'm using is sensitive so please excuse the made up nonsense!

    Hopefully you can see the attached spreadsheet - I have the Main Order Sheet which is what I input the data on then instead of highlighting each relevant department and copying over to the appropriate sheet, I want to have a column where I put the designated department in and the row automatically copies/populates over to that sheet. In the example, I have shown this using different colours, i.e. when I input data under the department name 'Blue' the entire row needs to automatically copy under the sheet 'Dept Blue'.

    Hope that explains and there's an easy enough solution
    Attached Files Attached Files

  5. #5
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    20,899

    Re: How to copy row to another sheet based on cell value - Macro or Formula

    Using formulae, set up a helper column in the Main sheet which identifies which department that each record belongs to and allocates a unique ID to each record. I have used column H in the attached file, with this formula in H2:

    =IF(A2="","-",A2&"_"&COUNTIF(A$2:A2,A2))

    Copy this down as far as you like - if you copy it beyond your existing data it will accommodate more records being added (the hyphens help to show where the formula is active). I've also coloured this blue in the attached file.

    Then in one of your subsidiary sheets you can add the department name (say in cell A1) and the column headers on row 3, and you can use this formula in A4:

    =IFERROR(INDEX('Main Sheet'!A:A,MATCH($A$1&"_"&ROWS($3:3),'Main Sheet'!$H:$H,0)),"")

    Copy this across to E4, and format the cells appropriately (especially the date and number fields), then you can copy this row of formulae down as far as you need to (I've copied to row 10 in the attached file).

    Then you can just CTRL-drag that sheet tab 3 times to set up the other department sheets - you will need to rename the sheets and put the department name in cell A1 of each new sheet.

    Hope this helps.

    Pete
    Attached Files Attached Files

  6. #6
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,581

    Re: How to copy row to another sheet based on cell value - Macro or Formula

    Try the attached file. I have modified the department names in column A to match the worksheet names. This would save you from having to type the designated department name manually. In column F, I have entered a drop down where you can select "Done" when you want to copy the row to the appropriate sheet. You would simply have to change the target range in the macro to match the column in your actual workbook. To make that change, right click on the tab name for the "Main Sheet" and click "View Code". Change "Range("F:F")" to suit your needs. Simply select "Done" in any desired row.
    Attached Files Attached Files

  7. #7
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    20,899

    Re: How to copy row to another sheet based on cell value - Macro or Formula

    I forgot to mention in my earlier submission (though I did refer to it in Post #3), that you could have a drop-down in cell A1 where you can select the department from a pre-defined list, so I've modified the file to show how this can be achieved. The list of departments is shown in column H, and these have been given the named range Department. This then drives the data validation drop-down in cell A1 - select your department and the display changes accordingly.

    In this case, you don't need individual sheets for each department, so I have deleted those.

    Hope this helps.

    Pete
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    07-15-2019
    Location
    North West
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: How to copy row to another sheet based on cell value - Macro or Formula

    Hi Guys, thanks again for the feedback - Both examples do the job however I do like the summary section of Pete's example. I have tried to edit it by adding additional departments but though it links on the main sheet, i.e. Typing Pink as a department generates cell value 'Pink_1' in Column H it does not copy over to the drop down list as an option on the Summary sheet even though I have also listed it under Departments section on the Summary sheet. Don't know if there's an easy fix or if you could advise how I can add further departments ?

    Can you also move Column H to Column S - It will just enable me to use this without trying to edit the Macro/Formula as above my skill set!

    Thanks again for the help

  9. #9
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    20,899

    Re: How to copy row to another sheet based on cell value - Macro or Formula

    The easiest way is to highlight column H and then CUT/Paste it to S1. To adjust the length of the named range, click on Formulas | Name Manager, then select the name Department from the list and you will see in the Refers to box that it currently says:

    =Dept_summary!$S$2:$S$5

    Just change the final number (shown in red) to what you want it to be and then click on Close - Excel will ask you to confirm that you want to make that change. There is no need to change any of the formulae, though you might need to copy them down further (on both sheets) if you have more data.

    If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

    Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

    Hope this helps.

    Pete

  10. #10
    Registered User
    Join Date
    07-15-2019
    Location
    North West
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: How to copy row to another sheet based on cell value - Macro or Formula

    I have been running with the spreadsheet and is seeming to do the trick perfectly - Thanks again for your help!

  11. #11
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    20,899

    Re: How to copy row to another sheet based on cell value - Macro or Formula

    You're welcome - glad it works for you.

    Pete

  12. #12
    Registered User
    Join Date
    07-15-2019
    Location
    North West
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: How to copy row to another sheet based on cell value - Macro or Formula

    Hi Pete - You helped me a while back on this forum with a spreadsheet and just wondered if you could advise.

    I have noticed the department summary tab has stopped bringing through the main sheet records from June 2020 onwards but can't find how I can extend this list/lookup - Any help on this would be much appreciated!

    I have tried viewing code on main sheet, this currently displays the below;
    Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("F:F")) Is Nothing Then Exit Sub
    If Target = "Done" Then
    Target.EntireRow.Copy Sheets(Range("A" & Target.Row).Value).Cells(Sheets(Range("A" & Target.Row).Value).Rows.Count, "A").End(xlUp).Offset(1, 0)
    End If
    End Sub

    Have also tried amending formula on the dept_summary tab to include greater range / copy and pasted this further down but still doesn't seem to appear to bring anything through past a certain date - see current formula below;
    =IFERROR(INDEX('Main Sheet'!A:A,MATCH($A$1&"_"&ROWS($3:198),'Main Sheet'!$S:$S,0)),"")

  13. #13
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    20,899

    Re: How to copy row to another sheet based on cell value - Macro or Formula

    I think that macro is from Mumps1's submission - my solution just uses two main formulae.

    You must ensure that the formula in the helper column on the Main Sheet is copied down to at least the bottom of your data - it doesn't matter if you copy beyond this. (This helper column was column H in the file above, but you subsequently asked for it to be moved to column S).

    Hope this helps.

    Pete

  14. #14
    Registered User
    Join Date
    01-30-2021
    Location
    Fort Wayne, IN
    MS-Off Ver
    Office 360
    Posts
    1

    Re: How to copy row to another sheet based on cell value - Macro or Formula

    Quote Originally Posted by Pete_UK View Post
    I forgot to mention in my earlier submission (though I did refer to it in Post #3), that you could have a drop-down in cell A1 where you can select the department from a pre-defined list, so I've modified the file to show how this can be achieved. The list of departments is shown in column H, and these have been given the named range Department. This then drives the data validation drop-down in cell A1 - select your department and the display changes accordingly.

    In this case, you don't need individual sheets for each department, so I have deleted those.

    Hope this helps.

    Pete
    This is great. I had use this approach to get individual sheets per unique value in a given cell, but I couldn't figure out how to adapt it. Your approach saved me serous time learning it the hard way.

    Thanks.

+ 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. [SOLVED] Macro to copy from one sheet to another, based on a cell in sheet1
    By amar05 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-05-2016, 02:04 PM
  2. Macro - Copy sheet and rename based on cell value
    By Nick.123 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-15-2013, 04:23 AM
  3. [SOLVED] Macro to Copy Data from one Sheet A to Sheet B based on value in cell on sheet A
    By scass in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-11-2012, 07:21 PM
  4. [SOLVED] Macro to copy formula to end of data based in another sheet
    By Wazations in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 08-31-2012, 10:50 PM
  5. Macro to copy rows to another sheet, based on cell value
    By hampton06 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-03-2012, 04:40 AM
  6. [SOLVED] Macro: Dynamic creation of sheet based on cell value then copy range to the new sheet
    By BeachRock in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 03-15-2012, 11:31 AM
  7. VBA Macro to copy cells over to another sheet based on the value of a cell
    By hani9041 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 03-01-2012, 03:28 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