vba Excel - 从另一个工作表中的工作表复制某些行

Excel - copy certain rows from worksheet in another worksheet

提问by Nikki

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.

我找到了解决这个问题的方法，但我不知道如何根据我的具体问题定制它们。我以前从未写过 marco 或任何 VBA 脚本，所以请具体说明你的答案。

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

我有一个 Excel 工作簿，其中一个工作表名为“食物”，另外四个工作表名为“小吃选择”、“早餐选择”、“午餐选择”和“晚餐选择”

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

“食物”工作表中的标题如下所示：

Food Amount Calories ... ETC ... Type

食物量 卡路里 ... ETC ... 类型

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

其中“类型”包含“早餐”“小吃”“午餐”“晚餐”或 2+ 选择的组合。

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!

先感谢您！

回答by Jüri Ruut

Main table with foods (Sheet 'Foods'):

带食物的主表（表“食物”）：

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.

A1：食物类型。为了匹配其他食物（午餐/谷物/早餐）的组合，它应该以“*”开头和结尾：*breakfast*而不是breakfast。也可以对公式进行更改。

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

然后我们在“食物”表上寻找“早餐”的第一次出现：

A3:

A3：

=IF(ISERROR(MATCH($A,Foods!A:A;0)),"",MATCH($A,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:

乙2：

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

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

D2：将 INDIRECT("Foods!B"&$A3)) 改为 INDIRECT("Foods!D"&$A3))，E 为 E2 等。

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

第一列下方 A 列中的其他行（从 A4 向下复制）：

=IF(ISERROR(A3+MATCH($A,INDIRECT("Foods!A"&A3+1&":A60000"),0)),"",A3+MATCH($A,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

我上传了一个电子表格示例到http://www.bumpclub.ee/~jyri_r/Excel/Type_of_food_on_another_sheet.xls

回答by Don

Looking for excel formula to copy entire rows throughout many sheets to a single sheet. The rows trying to copy have a "x" in the first column.

寻找 excel 公式以将许多工作表中的整行复制到单个工作表中。试图复制的行在第一列中有一个“x”。

Ex.

前任。

Sheets are set up like this:

表格设置如下：

Columns 1 2 3 4 5 Rows 1 Sell Priority WHID Sequence SAT 2 X 1 FG 1.FG.01
3 1 FG 1.FG.01

列 1 2 3 4 5 行 1 卖出优先权 WHID 序列 SAT 2 X 1 FG 1.FG.01
3 1 FG 1.FG.01

Have many rows and many columns and many sheets.

有许多行、许多列和许多工作表。

Looking to get each 'X' in first column to pull to a new sheet.

希望将第一列中的每个“X”拉到新工作表中。

Any help?

有什么帮助吗？