Excel VBA - 链接来自不同工作表的行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12807198/
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
Excel VBA - link rows from different sheets
提问by user1732763
Scenario: I can't post the actual workbook due to sensitive materials but I'll explain it as best as possible.
场景：由于敏感材料，我无法发布实际的工作簿，但我会尽可能对其进行解释。
Each Sheet is divided into branches i.e.: Sheet2 is Branch 2, Sheet3 is Branch 3 and so on. For each sheet the column are the same. Column A has the branch number in it. Column B has irrelevant information so i just hide that column. Column C has a system number (specific to each account.
每个工作表分为分支，即：工作表 2 是分支 2，工作表 3 是分支 3，依此类推。对于每张纸，列都是相同的。A 列中有分行编号。B 列有不相关的信息，所以我只是隐藏了该列。C 列有一个系统编号（特定于每个帐户。
Intention: I want to create another sheet called CallOuts.
I want to copy some rows (from various branches) and paste them onto the 'Master sheet' (CallOuts) I can work on the CallOutssheet instead of going to each branch. So that whenever I edit a cell it will also update/change that exact same cell in the branch sheet and vise versa with the master sheet.
意图：我想创建另一个名为CallOuts. 我想复制一些行（从各个分支）并将它们粘贴到“主表”（CallOuts）我可以在工作CallOuts表上工作，而不是去每个分支。因此，每当我编辑一个单元格时，它也会更新/更改分支表中完全相同的单元格，反之亦然。
Problem: I know MS Exccel has a "Paste Special" function where it adds the cell. The problem with that is it links the cell# so if I sort the Master sheet it will replace the row into the wrong branch sheet.
问题：我知道 MS Exccel 有一个“选择性粘贴”功能，它可以添加单元格。问题在于它链接单元格#，因此如果我对主表进行排序，它将将该行替换为错误的分支表。
E.g.: If System# J112 is in branch 2 sheet, row 2 and I have the link pasted in Row 4 in the master sheet, if I make updates on the Master sheet and then re-sort it and the System# now moves to Row 2 (on the master sheet) whatever is in Row 4(on the master) will now be in Row 2, Branch 2 sheet.
例如：如果 System# J112 位于分支 2 工作表的第 2 行，并且我将链接粘贴在主工作表的第 4 行中，如果我在主工作表上进行更新，然后对其重新排序并且系统# 现在移动到行2（在母版上）第 4 行（在母版上）中的任何内容现在都将位于第 2 行，第 2 行工作表中。
I was thinking maybe a macro where I could copy and paste the entire row from the master sheet. Do some type of case selection to check which branch is in column A and then find the same system # on the branch sheet then paste the whole row.
我在想也许是一个宏，我可以在其中复制和粘贴母版表中的整行。进行某种类型的案例选择以检查 A 列中的哪个分支，然后在分支表上找到相同的系统 # 然后粘贴整行。
My VBA level is novice at best so any help would be appreciated.
我的 VBA 水平充其量只是新手，因此将不胜感激。
采纳答案by Stepan1010
OK, So I saw your attempt below. I am not sure how your code works but it seems like you were on the right track. However, I don't think the Case syntax is appropriate for this situation. You simply need a macro that will read what is in your "CallOuts" sheet and use that data to update your individual branch sheets. This macro should work for you:
好的，所以我在下面看到了您的尝试。我不确定您的代码是如何工作的，但您似乎走在正确的轨道上。但是，我认为 Case 语法不适合这种情况。您只需要一个宏来读取“CallOuts”工作表中的内容并使用该数据更新您的各个分支工作表。这个宏应该适合你：
Sub UpdateRecordsFromMasterSheet()
Dim wksht As Worksheet
Dim wkb As Workbook
Dim row As Range
Dim Row_to_Update As Range
Dim sysnum As Variant
Set wbk = ThisWorkbook
Application.ScreenUpdating = False
For Each row In Sheets("CallOuts").UsedRange.Rows
row.EntireRow.Copy
sysnum = row.Cells(1, 3).Value
For Each wksht In ActiveWorkbook.Worksheets
For Each Row_to_Update In wksht.UsedRange.Rows
If Row_to_Update.Cells(1, 3).Value = sysnum Then
Row_to_Update.PasteSpecial
End If
Next Row_to_Update
Next wksht
Next row
Application.ScreenUpdating = True
End Sub
This assumes you have your system number in column "C". So here is what I am imagining: Each time you assign an employee a bunch of rows - your employee updates those rows - and then you paste that data back into the "CallOuts" sheet. Then you run this macro and it will update the individual branch sheets based on what is in the "CallOuts" sheet.
这假设您在“C”列中有您的系统编号。所以这就是我的想象：每次您为员工分配一堆行时 - 您的员工更新这些行 - 然后将该数据粘贴回“CallOuts”表中。然后运行这个宏，它将根据“CallOuts”表中的内容更新各个分支表。
If your a visual person here is how it will look:
如果你的视觉人物是这里的样子：
Here is the updated callouts sheet:
这是更新的标注表：
Here is how the branches will look before the macro:
以下是分支在宏之前的外观：
Here is the updated branch after the macro runs:
这是宏运行后更新的分支：
Try it out and let me know how it works. Note: this is just a start to show you how to do something like this - you could make this process more efficient by having the macro loop through the actual workbooks that the employees submit to you so you wouldn't have to copy and paste the data into your "CallOuts" sheet every time. Good Luck.
试试看，让我知道它是如何工作的。注意：这只是向您展示如何做这样的事情的一个开始 - 您可以通过让宏循环遍历员工提交给您的实际工作簿来提高此过程的效率，这样您就不必复制和粘贴每次都将数据放入“CallOuts”表中。祝你好运。
