Excel - copy certain rows from worksheet in another worksheet

Question

I have found ways to solve this issue but I don't know how to tailor them to my specific problem. I've never written a marco or any VBA script before, so please be specific with your answers.

I have an Excel workbook with one worksheet named "foods", and four other worksheets named "snack choices", "breakfast choices", "lunch choices", and "dinner choices"

The heading in the "foods" worksheet looks like this:

Food Amount Calories ... ETC ... Type

Where "Type" contains "breakfast" "snack" "lunch" "dinner" or a combination of 2+ choices.

I'd like the entire row to copy to the corresponding "snack choices", "breakfast choices", "lunch choices", and "dinner choices" worksheet according to the value of the "Type" field in the "foods" worksheet.

For example, if I enter a new row into the "foods" worksheet with the "Type" field as "snack / breakfast" I'd like for the row to automatically appear in the "breakfast choices" and "snack choices" worksheets. (I could remove the slash or replace it with another character if that makes it easier)

Thank you in advance!

I've never written a marco or any VBA script before, so please be specific with your answers. -> break down your needs into steps (write down your logic in English), then learn how to do each step in VBA, then put them altogether. Some VBA methods that will help are For Each Next Loops, AutoFilter (possibly), and InStr. If you develop some code and are stuck, please post it here. We can help much better if we have code to work with. — Scott Holtzman

Jüri Ruut Jüri Ruut · Accepted Answer · 2012-10-31T21:16:28

Main table with foods (Sheet 'Foods'):

Main table with foods (Sheet 'Foods')

Table for breakfasts:

Table for breakfasts

In this table:

A1: Type of food. In order to match combinations with other foods (Lunch/Cereal/Breakfast), it should start and end with '*': *breakfast* instead of breakfast. It's possible also to make a change in formulae.

Then we look for the first occasion of "breakfast" on the sheet 'Foods':

A3:

=IF(ISERROR(MATCH($A$1,Foods!A:A;0)),"",MATCH($A$1,Foods!A:A,0))

As a result, a number of the matching row will be shown. If no match, a blank cell will be shown.

Then we match other data:

B2:

=IF(A3="","",INDIRECT("Foods!B"&$A3))

D2: change INDIRECT("Foods!B"&$A3)) to INDIRECT("Foods!D"&$A3)), E for E2 etc.

Other rows in column A below the first one (copied downwards from A4):

=IF(ISERROR(A3+MATCH($A$1,INDIRECT("Foods!A"&A3+1&":A60000"),0)),"",A3+MATCH($A$1,INDIRECT("Foods!A"&A3+1&":A60000"),0))

If no match, a blank cell will be shown.

I uploaded a spreadsheet example to http://www.bumpclub.ee/~jyri_r/Excel/Type_of_food_on_another_sheet.xls

Excel - copy certain rows from worksheet in another worksheet

2 Answers