vba 为每一行创建一个新工作表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2101815/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Create a new sheet for each row
提问by dassouki
In an Excel sheet, I have roughly 30 rows x 100 columns of data. Each row represents a different "client". For each client, I've create a summary sheet that is emailed to them and that also contains all the information from my main sheet
在 Excel 工作表中，我有大约 30 行 x 100 列的数据。每行代表一个不同的“客户”。对于每个客户，我都创建了一个汇总表，通过电子邮件发送给他们，其中还包含我的主表中的所有信息
Is there a way for Excel to create a new sheet based on some template sheet when I add a new row to my main sheet and fill it with the appropriate data?
当我向主工作表添加新行并用适当的数据填充时，Excel 是否可以基于某个模板工作表创建新工作表？
回答by Gnoupi
I will give you my opinion about your need, the way I see it, at least. It is not a "ready to use" solution, however, only some ideas about the way to do that.
我会就你的需要，至少以我的看法，给你我的意见。然而，这不是一个“随时可用”的解决方案，只是关于如何做到这一点的一些想法。
From what I know, there is no way to track insertion of a row in Excel. So you would require a VBA function to be activated on a button, for example.Actually, there is, see Lunatik's answer.
据我所知，无法在 Excel 中跟踪行的插入。因此，例如，您需要在按钮上激活 VBA 功能。实际上，有，请参阅 Lunatik 的回答。
This function would loop over all rows in your main sheet, and create a new sheet when necessary (you would need preferably a unique id for each client, it could be a simple index on the main sheet, depending on the line).
此函数将遍历主工作表中的所有行，并在必要时创建一个新工作表（您最好为每个客户端提供一个唯一的 id，它可以是主工作表上的一个简单索引，具体取决于行）。
You would create at first your template sheet, with a specific name, and eventually hide it (to not have it in the visible tabs). When I say that the function would create, it would in fact copy this template sheet and give it a unique name (the id I mentioned earlier). You can find ways to copy sheets at this link.
您将首先创建具有特定名称的模板表，并最终将其隐藏（使其不在可见选项卡中）。当我说函数会创建时，它实际上会复制这个模板表并给它一个唯一的名称（我之前提到的 id）。您可以在此链接中找到复制工作表的方法。
A second operation to do, would be to put data from the row in the main sheet, to the template sheet (if I understood correctly your requirement), which is not really complicated to do in VBA.
要做的第二个操作是将数据从主表中的行放入模板表（如果我正确理解您的要求），这在 VBA 中做起来并不复杂。
回答by Lunatik
If you need this to happen automaticallyon the addition of a row then you would need to use the Worksheet_Changeevent to capture the completion of a new row.
如果您需要在添加行时自动发生这种情况，那么您需要使用该Worksheet_Change事件来捕获新行的完成情况。
This would then generate a new workbook from the template, copy across the necessary ranges then save the new file somewhere, much as Gnoupi says
然后这将从模板生成一个新的工作簿，在必要的范围内复制，然后将新文件保存在某处，就像 Gnoupi 所说的
All this is relatively trivial with VBA, but unfortunately if you aren't familiar with VBA then isn't a simple case of "Do X then do Y in Excel" so I think you may struggle, even with sample code posted here.
所有这些对于 VBA 来说都是相对微不足道的，但不幸的是，如果您不熟悉 VBA，那么“在 Excel 中做 X 然后做 Y”就不是一个简单的例子，所以我认为即使在这里发布了示例代码，您也可能会遇到困难。
Even if I created a dummy model that did what you require, functionally at least, then customising it to your particular needs may difficult if you are not used to working with Excel programmatically.
即使我创建了一个虚拟模型来满足您的要求，至少在功能上，如果您不习惯以编程方式使用 Excel，那么根据您的特定需求对其进行自定义可能会很困难。
Edit
I've created a verysimple demonstration of how this could work: http://drop.io/4clxof3- note this example doesn't include the event handling for adding a new row, has almost no validation or error handling and makes sweeping assumptions about almost everything(!)
编辑
我创建了一个非常简单的演示来说明它是如何工作的：http: //drop.io/4clxof3- 请注意，此示例不包括添加新行的事件处理，几乎没有验证或错误处理，并且使对几乎所有事情的全面假设（！）
If you feel comfortable using this a basis for developing your own solution then great. If the whole VBA thing is foreign to you then it may be time to call in reinforcements :)
如果您觉得使用它作为开发自己的解决方案的基础很舒服，那就太好了。如果整个 VBA 的事情对你来说都是陌生的，那么可能是时候召集增援了:)
回答by dassouki
i was wondering if it was possible with no error catching. Simply just have a VBA code that takes each row of the Excel Document - Creates a file for each row and then at the end combines the total files in a folder into one?
我想知道是否有可能没有错误捕获。只需有一个 VBA 代码来获取 Excel 文档的每一行 - 为每一行创建一个文件，然后最后将文件夹中的所有文件合并为一个？
I know sounds weird.. but is this possible?
我知道听起来很奇怪……但这可能吗？
