Excel - Copy row to different sheet if row contains "x"

Question

Hi everyone,

I am attempting to get an entire row to copy onto a new sheet if one of the cells in said row contains a specific value.

Specifically: I want to copy an entire row of log data, depending on the truck used in column C, to an existing sheet for each truck.

Any thoughts? I've hunted for IF/THEN scenarios and SUMIF, etc... no luck yet.

Thanks!

Answer 1

Interjecting a few additional thoughts;

Perhaps I'm underestimating your requirement based on limited information, but for no more than what you described a very simple approach would be to just filter the list in place, then copy the found record(s) & paste to the other sheet.

If you need something more dynamic, you might investigate the use of;

• Pivot Tables, &
• Advanced Filter

in addition to the suggestions being offered by others.

Another approach would be to store the data for each 'truck' on a separate sheet of its own. It could be aggregated at any time for all, or any combination of, trucks.

Answer 2

Hi,

You may refer to my solution at this link.

Hope this helps.

Answer 3

Hi Aaron,

Based on your description, you would like to copy an entire row if column C contains the specific criteria. I suggest you use VBA code to achieve it. You can f as below.

1.    Go to Developer, click Visual Basic under Code.

2.    Add the code below. For example, rows which contains RED in Column C in Sheet 1 will be copied to Sheet 2.

Sub MoveData()

Sheet1.[C1:C5].AutoFilter 1, "*RED*"

Sheet1.[A1:E5].copy Sheet2.[A1]

Sheet1.[A1].AutoFilter

End Sub

3.    Click Run.

For more details: Create a macro in Excel 2016 for Mac.

Regards,

Yoga

Answer 4

The other answers are close, but not entirely correct. You need a VBA program to do what you want to do. A formula (like If then or SUMIF) can NOT change the contents of any cell other than the cell it is in. therefore, you need a routine that scans a specified range for a value and when found moves that row to another place.

If you want some specific help, you'll have to be a lot more specific about what the data looks like, what the sheets for each truck look like, where does the row from the original sheet go in the truck's sheet - (next row, specific row, what about duplicates) etc. etc.

Answer 5

... You need a VBA program to do what you want to do...

- I was figuring that's the conclusion. What would you recommend?

If you want some specific help, you'll have to be a lot more specific about what the data looks like, what the sheets for each truck look like, where does the row from the original sheet go in the truck's sheet - (next row, specific row, what about duplicates) etc. etc.

- Here's a screenshot of a both tabs. I edited the truck's tabs for appearances, but they don't need to be formatted this way. Each of the truck tabs are identical, minus title in the headers. I kept the columns the same globally to make it easier. The end goal is to separate all data entered in sheet 1 (auto-populated from an app) onto the nicer looking truck tabs for reporting purposes. If I can get that figured out, I'll do the same by driver at some point as well. Either way, I imagine it would be the same process.