敬请朱荣兴老师帮忙写一个多表求和的代码

laoshenji

        尊敬的朱荣兴老师，昨天刚麻烦您给学生写了个多表查询的代码，按理说，学生不该再给您添麻烦，应该让您休息一下大脑，因为写代码真是太费心劳神了。请老师不要怪学生贪心，学生担心：如果不抓住这次难得的机会，以后再找老师您相助恐怕就很困难了（尤其是能遇到一个精通VBA的高手则更难）。

        本贴的求助附件是学生5年前在本论坛的求助结果（当时求助的要求见附件《汇总表》右侧的文字），但由于其运算速度特慢，每登记一笔明细表就要等待较长的确认时间，这个问题导致本模板一直没有投入实际运用。因此，特拜求朱老老师在万忙之际抽出一点时间帮学生看看。学生期盼老师的答案。谢谢了！

新的问题及要求是：

    1、将《汇总表》的按月多表汇总求和函数改为VBA代码。无论《汇总表》B列“材料名称”的多少，其动态合计始终显示在该表的最末行。。

    2、打开本模板时《汇总表》出现短暂性的变异，这个问题一直未获得解决。

    3、每当点击各明细表（不含《辅助表》）的保护区域，就跳转到《汇总表》的原因不明。

    5、《汇总表》始终放在工作表的最前面，至于《辅助表》是否放在最后由老师酌情处理。

  6、《辅助表》不要用VBA设置保护（好像附件已是如此），因为本模板仅供一个年度使用，每年初须将上年末的余额复制于此表中供各明细表引用。

    7、明细表（不含《辅助表》）可增减和更改标签名。

    8、其他未尽事项，请老师看着办。

补充内容 (2016-6-13 13:54):
这个帖子就这样沉下去了？？？？？？

laoshenji

静候大师们的答案！

chenbiao2012

很简单的表，楞给弄成复杂的不像样

一指禅62

真难为朱老师了。

楼主的数据管理本末倒置了吧，常规是由源数据生成明细账、总账、分类账。

laoshenji

一指禅62 发表于 2016-4-12 16:54
真难为朱老师了。

楼主的数据管理本末倒置了吧，常规是由源数据生成明细账、总账、分类账。

首先谢谢一指禅62老师的关注。这个求助附件实际上是一个“商品明细账+汇总表”的模式，并没有本末倒置，也是先记明细账，然后再由《汇总表》按月动态汇总求和。总而言之，就是将原来的函数公式直接替换成VBA就可以了。

laoshenji

chenbiao2012 发表于 2016-4-12 15:25
很简单的表，楞给弄成复杂的不像样

谢谢chenbiao2012老师关注。很简单？请你给个答案，让学生学习学习！

laoshenji

朱荣兴 发表于 2016-4-12 15:10
感谢你的太爱，惶恐之至，
1，请提供vba工程保护密码，
2、请提供工作表保护密码

老师过谦了。学生能得到朱老师的相助真是幸运。
《汇总表》好像没有保护密码，若有应与VBA工程密码相同都是123456
现将原求助时得到的回复复制于此，供老师参考。
楼主的要求，我需要逐一解答：
问题1、《汇总表》始终固定在工作簿的最前面，《辅助表》始终固定在工作簿的最后面。随时可能增加的新品名工作表（与“A”至“服装”工作表中的格式和公式完全相同），均在《辅助表》之前插入或者复制。也就是说，所设置的VBA必须满足随时在《辅助表》之前增加若干个新工作表（如将工作表增加至200个）这一特定要求。
答：这已经不是问题，我已经使用代码解决了！
问题2、《汇总表》单独设置公式及其公式保护密码，《辅助表》仅在C3单元格有一个返回《汇总表》的超链接公式。也就是说，所设置的VBA中唯独不对此两个工作表（分别固定在工作簿的最前和最后）进行保护，留着用手工设置。因为《汇总表》中的公式和格式，与“A”至“服装”（包括以后增加的同格式和同公式工作表）是不相同的。
答：这也不是问题，我已经使用代码解决了！
问题3、“A”至“服装”（包括以后增加的同格式、同公式工作表）的计算公式区域暂设定至第800行号，以后可能会根据业务量的大小酌情增加或减少数据行（主要考虑增加较多工作表后，闲置的数据行将会影响整个模板的运算速度）。所以说，恳请大师务必将增加工作表、增加或删减工作表数据行的VBA代码加以注释，以便本人根据实际需要进行修改。
答：我使用代码自动识别输入。只要你输入任何数据，代码会自动添加公式。所以，不需要预设定到 800行。
问题4、设置VBA工程保护密码，以达到有效保护计算公式的目的。
答：VBA 工程保护密码是防止别人误操作或修改代码，不是为了保护计算公式。保护计算公式是通过保护工作表来完成的。在附件中我已经通过代码实现了保护工作表，密码也是 123456。不过，这个工作簿再也不能设置成共享工作簿了，否则会出错的。因为共享工作簿是不允许对工作表进行加密码保护和解密码去保护操作的！切记！
问题5、另外，本工作簿所有返回《汇总表》的超链接均出现“引用无效”问题，还望大师诊断一下。
答：超链接问题已经全部解决。
问题6、由于本人对VBA一点也不懂，有关VBA方面的内容，烦请大师说详细一点，以便本人照葫芦画瓢。比如：
（1）将工作表增加至100个或者200个后，保护100个或200个工作表公式（不含固定在最前和最后的两个表）的VBA代码应该怎么修改？
（2）将每个工作表的数据行增加150行或者减少100行后，应如何进入VBA编辑器分别修改VBA代码？VBA代码修改后又该如何确认和退出VBA编辑器（本人原曾将在网上下载的VBA，复制于本人设置的工作簿后总是不能成功）？
（3）如何设置和撤销VBA工程密码，以便进入VBA编辑器进行以上所提及的相关操作。
答：第（1）和第（2）小问题已经无需修改 VBA 代码。尽管操作就是了。第（3）小问题希望楼主自己试图看我写的代码就知道了！

laoshenji

朱荣兴 发表于 2016-4-12 15:10
感谢你的太爱，惶恐之至，
1，请提供vba工程保护密码，
2、请提供工作表保护密码

以下是原帖中本人对老师的第1个答案的评介（供杨老师参考）：
1、经测试，本人觉得大师的杰作出乎预料的好：一是使在工作簿的最前、最后工作表中间增加新的工作表变得很方便；二是每个工作表的数据行，可以根据实际业务量的多少而自动延伸，这可解决了原来最担心的运算速度问题。然而，本人对工作簿中间工作表的公式进行批量复制后，这一功能已经出现了问题（例如，没有实际内容的区域仍有实线数据行、有实际内容的区域反而单元格实线残缺不齐）恳请大师诊断一下。但由于本菜鸟对VBA一窍不通，在此请问大师：在该工作簿中，工作表和数据行有没有数量上的限制？
2、经学生实践，对每一个工作表的原保护密码解除后还可以重新设置新的保护密码，但却需要逐一工作表撤销保护和逐一工作表重新设置保护。请教大师：今后可能还要根据实际需要对工作簿中的计算公式、列宽、行高和字体等进行改进，那么是否还是不能实现批量设置工作表保护密码？同时请教大师：工作簿中的计算公式能否进行修改?如果可以修改会不会影响到模板的正常操作？（这个问题如果不解决，将会严重影响到模板的实际应用）
3、本人在测试复制工作表和撤销工作表并修改计算公式等事项的过程中，当点击返回《汇总表》的超链接时突然显示“无法打开指定的文件”的提示语，继而出现死机现象。强制关机并重新启动后，发现所有往返超链接均失去链接功能。此突然事故使得本人很茫然且不知所措，恳望大师指点造成此问题的原因以及今后防范此类问题发生的相关措施。另外冒昧地请问大师一句：除工作簿最前和最后的工作表外，其他工作表（包括以后新增的工作表）的超链接是否也可以一并进行公式保护（VBA的也行）？
4、经测试，在模板中每当输完1个单元格的数据进而移至下一单元格时，系统确认所输入数据的速度有点迟缓（即有短暂的停顿，确认速度不太敏捷），请问大师这是何原因，应当如何改进？我担心，如果《汇总表》中的公式设置并运转后，数据的确认速度是否会变得更慢！
5、特别需要向大师汇报的是，本人将《汇总表》的公式复制后发生异变，已经不能像原来那样正确按月显示汇总数据。原来打开模板时，既没有“此工作簿包含其他数据连接……”之类的提示语，《汇总表》中的计算公式中也没有本工作簿的地址。这个问题敬请大师彻底解决一下，如果能用VBA解决更好（可以提高运算速度）！
6、还有一个因学生草率和疏忽而铸成的大错，即本人只顾请教大师批量设置工作表保护密码，却没想到计算公式的保护区域已被VBA代码作了预先限定，因而导致目前的模板因缺失必要的计算公式而不能投入使用。现将所疏漏的计算公式进行了补全（即除最前、最后工作表外，每个工作表的G列、J列和K列都补全了相关计算公式），烦请大师按照新的计算公式区域重新对工作表进行保护。由此添加的麻烦，学生只有向大师道声：“对不起！”同时，对大师不厌其烦的热心相助再次表示衷心地感谢！
以下是重传的附件，该附件既包含了上述所反映的全部问题，同时也包括了《汇总表》的计算公式和其他工作表（不含《辅助表》）的补全公式。

laoshenji

朱荣兴 发表于 2016-4-12 15:10
感谢你的太爱，惶恐之至，
1，请提供vba工程保护密码，
2、请提供工作表保护密码

以下是原帖中本人对老师的第2个答案的评介（供杨老师参考）：
1、经测试，本人觉得大师的杰作出乎预料的好：一是使在工作簿的最前、最后工作表中间增加新的工作表变得很方便；二是每个工作表的数据行，可以根据实际业务量的多少而自动延伸，这可解决了原来最担心的运算速度问题。然而，本人对工作簿中间工作表的公式进行批量复制后，这一功能已经出现了问题（例如，没有实际内容的区域仍有实线数据行、有实际内容的区域反而单元格实线残缺不齐）恳请大师诊断一下。但由于本菜鸟对VBA一窍不通，在此请问大师：在该工作簿中，工作表和数据行有没有数量上的限制？
我写的代码会自动添加公式，无需再复制公式。新的附件中我稍作修改，一旦你删除从第 8行往下的任一单元格内容，会询问是否要删除整行？根据使用者的选择作相应的处理。
2、经学生实践，对每一个工作表的原保护密码解除后还可以重新设置新的保护密码，但却需要逐一工作表撤销保护和逐一工作表重新设置保护。请教大师：今后可能还要根据实际需要对工作簿中的计算公式、列宽、行高和字体等进行改进，那么是否还是不能实现批量设置工作表保护密码？同时请教大师：工作簿中的计算公式能否进行修改?如果可以修改会不会影响到模板的正常操作？（这个问题如果不解决，将会严重影响到模板的实际应用）
实际上我已经将工作表进行了设置，不用解密就可以随时进行调整和编辑。当然用鼠标是无法选中的！只有在工作表左上角的地址栏内直接写入单元格地址（比如 L7），然后就可以在编辑栏内填写内容了！
但有一个问题，在新的附件中，我假设整个工作表中除第 6行数据除外，其他所有行的第 10 — 12 列（即 J、K、L、M、N 列）单元格内的公式与第 7行的相同。所以，只要添加新行，则自动复制第 7行的公式。如果楼主编辑第 7行的公式后，会自动将这些公式向下复制到所有有数据的行。
如果不是这样，则很难自动添加公式的。
不过，如果只是修改某行的公式（只要不是第 7行），你是可以使用上述方法单独修改的！但只要你修改第 7行的公式，则这些单独的公式会被全部刷新的！
3、本人在测试复制工作表和撤销工作表并修改计算公式等事项的过程中，当点击返回《汇总表》的超链接时突然显示“无法打开指定的文件”的提示语，继而出现死机现象。强制关机并重新启动后，发现所有往返超链接均失去链接功能。此突然事故使得本人很茫然且不知所措，恳望大师指点造成此问题的原因以及今后防范此类问题发生的相关措施。另外冒昧地请问大师一句：除工作簿最前和最后的工作表外，其他工作表（包括以后新增的工作表）的超链接是否也可以一并进行公式保护（VBA的也行）？
我本来将工作簿的名称直接作为字符串写在超链接里面的，只要楼主重新命名了工作簿的名称，则会自动去找原来的工作簿。如果找不到肯定会出错甚至会死机。新附件中已经修改，可以自动获得本工作簿的名称，所以，应该不再有这个问题！
4、经测试，在模板中每当输完1个单元格的数据进而移至下一单元格时，系统确认所输入数据的速度有点迟缓（即有短暂的停顿，确认速度不太敏捷），请问大师这是何原因，应当如何改进？我担心，如果《汇总表》中的公式设置并运转后，数据的确认速度是否会变得更慢！
那是代码再后台复制公式及格式！
5、特别需要向大师汇报的是，本人将《汇总表》的公式复制后发生异变，已经不能像原来那样正确按月显示汇总数据。原来打开模板时，既没有“此工作簿包含其他数据连接……”之类的提示语，《汇总表》中的计算公式中也没有本工作簿的地址。这个问题敬请大师彻底解决一下，如果能用VBA解决更好（可以提高运算速度）！
这个问题与我的代码无关，而是楼主的“汇总表”里大量引用了另一个名为“C:\DOCUME~1\审计\LOCALS~1\Temp\Rar$DI00.281\[！！！商品明细（只有函数，为什么含“宏”）.xls]工作簿”内“汇总”工作表的内容！
6、还有一个因学生草率和疏忽而铸成的大错，即本人只顾请教大师批量设置工作表保护密码，却没想到计算公式的保护区域已被VBA代码作了预先限定，因而导致目前的模板因缺失必要的计算公式而不能投入使用。现将原遗漏的计算公式进行了补全（即除最前、最后工作表外，每个工作表的G列、J列和K列都补全了相关计算公式），烦请大师按照新的计算公式区域重新对工作表进行保护。由此添加的麻烦，学生只有向大师道声：“对不起！”同时，对大师不厌其烦的热心相助再次表示衷心地感谢！
请参考第 2个问题的回答。