generate report if cell is "EXPIRED"

[hamistasty]

Was hoping for some assistance or even a start-

On tab change:

I'd like code to check every column of a spreadsheet in range A & i to G & i, with i being until column A value is nothing.

So I'd like the code to check for the words "EXPIRED" and "RENEW" and then take the value of column A of that row, and put it into a new sheet.

For example:

On Sheet "employee" the code checks every cell in a range for the words "EXPIRED" and "RENEW" and finds that there are two found on different rows. It takes the first one found, copies the column A value of that row (the employees name) and puts it into the tab "report" and in the column next to that it puts the word "EXPIRED" because that was the word that was found. Then it copies the employee name of the second word found which was "RENEW", and pastes that into the new sheet underneath the last employee put in there, and again copies and pastes the word RENEW next to that column in the same row.

 

[hamistasty]

Any assistance? please

 

[jbeaucaire]

This is what AUTOFILTER is for. You can do this in seconds with it, and record it into a macro of your own if needed.

1) Click on row1 of the data table
2) Activate the Data > Filter > Autofilter
3) Use the new drop down in the correct column to apply a Custom filter for:

Contains: EXPIRED OR Contains: RENEW

4) Copy the column A and column ?? values to another sheet
5) Turn off the Autofilter.


Record those steps and see if you can use the resulting code again, tweak the filtering range to a range deep enough to copy all the possible results.