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!

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

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

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

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

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

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

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

You're welcome - glad it works for you.

Pete

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)),"")

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

Originally Posted by Pete_UK

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.