Copying Rows Based on Cell Value

[Darkcloud617]

Hello,

I have a VBA code that seems to almost work but I am having an issue with 2 parts of it I would appreciate some help with.

I have 2 sheets, 'Totals' and 'Data'. 'Totals' is essentially the consolidated version of the 'Data' sheet. The 'Data' sheet has a list of employees in column A with other info filling out to the columns to the right. I am needing a VBA code that will copy that info over to 'Totals' based on cell G4 in 'Totals'. If the word in G4 matches text in column A in the 'Data' sheet then copy the row to the first blank cell starting at C14 on the 'Totals' sheet.

Here is what I am looking to do:

1. If text in G4 says Joe Schmo on the 'Totals' sheet then search the 'Data' sheet column A for a match. G4 will most likely be a drop down menu with the names.
2. If match is found then copy text (only) from row and paste to 'Totals' starting at C14 on first blank row (there may be 10-20 rows that are copied at a time)

Here is the code I have been working with. After some changes I now have errors:

Please Login or Register  to view this content.

As you can see, I am having an issue figuring out how to change "If c = "Joe Schmo" Then" to say "If C = Target.G4 Then" or something like that. Meaning that instead of specific text in the VBA code it actually reads the text in G4 on 'Totals'. I also am having an issue specifying that the cell it should paste on starts at C14 instead of A1 on "Totals". Lastly, I cant seem to remember how to say that I want text only as opposed it to keeping the formatting from the 'Data' sheet.

Just to make it as easy as possible I included a test workbook.

Thank you in advance for any assistance.

[AlphaFrog]

This uses Autofilter in the Data sheet. It assumes the top row is a header.

Please Login or Register  to view this content.

[sintek]

Place this in Sheets("Totals") sheet Module...
Code runs on selection in G4

I am assuming that all other should be cleared...

Please Login or Register  to view this content.

Place this in std module to populate dropdown with unique list

Please Login or Register  to view this content.

[Darkcloud617]

Thank you both for the code, I really appreciate it. I actually could not get Alphafrogs to load into the Data sheet. It seems to create a filter but not actually move data over to Totals?

Sinteks is working but I have one question. The code moves the data over based off the drop down (which is great, I didnt even think to use it in that manner) but it doesnt respond well if the person on the 'Data' sheet only has 1 row of data. It returns the error 'subscript out of range' and then will not work again. Any idea how to correct that? It should still return if there is only 1 row of data which I didnt think about initially. It errors out on this line in the Private Sub "Range("C14").Resize(UBound(Arr, 1), UBound(Arr, 2)) = Arr"

Thank you both for the assistance. I really really appreciate it.

[sintek]

Please Login or Register  to view this content.

[Darkcloud617]

This works absolutely perfectly and will save us tons of time. Thank you so much. I really appreciate your help.

[sintek]

.........................
Thanks.png

[Darkcloud617]

Hello, this is the first time we will be using this report for the month and it is getting an error message:

"Removed Feature: Data validation from /xl/worksheets/sheet1.xml part"

any ideas on what could be causing that? The data does not seem to be transferring over?

[AliGW]

You might want to remove the solved tag if you require further assistance.