我有 3 個不同的人使用的 3 個作業表。如果選擇了“Res”，則作業表“Builder Contact”需要輸入到作業表“Res Jobs”中，如果選擇了“Comm”，則需要輸入到“Comm Jobs”中。被復制的資訊不會進入同一列（例如，“Builder Contact”第 1、10、2、4、5 列將分別是“Res Jobs”第 1、2、3、7、8 列）。
當從“Builder Contact”表的下拉選單中選擇“Res”或“Comm”時，我還需要自動更新它。我當前的代碼目前可以做到這一點，但我每次都必須點擊運行，并且由于回圈，它會重復所有內容。但是回圈是我當前如何獲得“x”值，我需要找到要復制所有資訊的行。
Sub Res_Comm()
Sheets("Builder Contact").Select
' Find the last row of data
FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
' Loop through each row
For x = 2 To FinalRow
' Decide if to copy based on column K (column with the drop down menu to select "Res" or "Comm")
ThisValue = Cells(x, 11).Value
If ThisValue = "Res" Then
Cells(x, 1).Copy
Sheets("Res Jobs").Select
NextRow = Cells(Rows.Count, 1).End(xlUp).Row 1
Cells(NextRow, 1).Select
ActiveSheet.Paste
Sheets("Builder Contact").Select
Cells(x, 10).Copy
Sheets("Res Jobs").Select
NextRow = Cells(Rows.Count, 1).End(xlUp).Row
Cells(NextRow, 2).Select
ActiveSheet.Paste
Sheets("Builder Contact").Select
Cells(x, 2).Copy
Sheets("Res Jobs").Select
NextRow = Cells(Rows.Count, 1).End(xlUp).Row
Cells(NextRow, 3).Select
ActiveSheet.Paste
Sheets("Builder Contact").Select
Cells(x, 4).Copy
Sheets("Res Jobs").Select
NextRow = Cells(Rows.Count, 1).End(xlUp).Row
Cells(NextRow, 7).Select
ActiveSheet.Paste
Sheets("Builder Contact").Select
Cells(x, 5).Copy
Sheets("Res Jobs").Select
NextRow = Cells(Rows.Count, 1).End(xlUp).Row
Cells(NextRow, 8).Select
ActiveSheet.Paste
' This column is asking for the source, which in this case would be the name of the user for "Builder Contact"
Cells(NextRow, 6).Value = "Dan"
ElseIf ThisValue = "Comm" Then
Cells(x, 1).Copy
Sheets("Comm Jobs").Select
NextRow = Cells(Rows.Count, 1).End(xlUp).Row 1
Cells(NextRow, 1).Select
ActiveSheet.Paste
Sheets("Builder Contact").Select
Cells(x, 10).Copy
Sheets("Comm Jobs").Select
NextRow = Cells(Rows.Count, 1).End(xlUp).Row
Cells(NextRow, 3).Select
ActiveSheet.Paste
Sheets("Builder Contact").Select
Cells(x, 2).Copy
Sheets("Comm Jobs").Select
NextRow = Cells(Rows.Count, 1).End(xlUp).Row
Cells(NextRow, 4).Select
ActiveSheet.Paste
Sheets("Builder Contact").Select
Cells(x, 4).Copy
Sheets("Comm Jobs").Select
NextRow = Cells(Rows.Count, 1).End(xlUp).Row
Cells(NextRow, 8).Select
ActiveSheet.Paste
Sheets("Builder Contact").Select
Cells(x, 5).Copy
Sheets("Comm Jobs").Select
NextRow = Cells(Rows.Count, 1).End(xlUp).Row
Cells(NextRow, 9).Select
ActiveSheet.Paste
Cells(NextRow, 7).Value = "Dan"
End If
Next x
End Sub
[建設者聯系方式][1][資源作業][2]
It won't let me add the photos directly yet, but hopefully the links work.
[1]: 
然后粘貼這段代碼：
Private Sub Worksheet_Change(ByVal Target As Range)
Dim s As Worksheet
Dim source_columns As Variant
Dim dest_columns As Variant
Dim next_row As Long
Dim x As Long
If Target.Column = 11 Then
If Target.Value = "Res" Then
Set s = Sheets("Res Jobs")
dest_columns = Array(1, 2, 3, 7, 8)
ElseIf Target.Value = "Comm" Then
Set s = Sheets("Comm Jobs")
dest_columns = Array(1, 3, 4, 8, 9)
Else
Exit Sub
End If
source_columns = Array(1, 10, 2, 4, 5)
next_row = s.Cells(s.Rows.Count, 1).End(xlUp).Row 1
For x = 0 To UBound(source_columns)
s.Cells(next_row, dest_columns(x)).Value = Cells(Target.Row, source_columns(x))
Next
s.Cells(next_row, 6).Value = "Dan"
End If
End Sub
uj5u.com熱心網友回復：
聽起來您希望用戶從下拉串列中進行選擇，然后運行您提供的代碼。如果是這樣，您想在作業表上放置一個“表單控制元件組合框”。您可以在功能區的開發人員選項卡上找到它。
將組合框放在作業表上后，右鍵單擊它并選擇“格式控制”
這將允許您配置控制元件。在“輸入范圍”下，選擇您希望出現在可能性串列中的值的范圍。在“單元格鏈接”下，將單元格放在您希望值去的地方。在該單元格中，您將獲得一個數字，指示選擇了哪個專案。更改您的代碼以根據該數字而不是 res/comm 采取不同的行動。
Finally, right-click the combobox and choose "assign macro" to choose the macro you want to run when the user makes a choice.
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/454703.html
標籤：excel vba copy-paste auto-update