Excel-如果从另一个工作表复制粘贴

I have a workbook which has 2 sheets.(Roles and roleUpdates). Role update sheet contains the role changes and new roles. It has more entry than the Roles sheet but I only want to deal with the entries that is both in the Roles and RoleUpdates. One of the colums in the RoleUpdates is called "status" and one of the options is roleChange. Roles sheet is the one that I want to update which has the older roles. Basically I want to look at the status column in the RoleUpdates and if it is "roleChange", then I want to copy that role column in the RoleUpdates sheet and paste it into Roles sheet based on the name. Example;

我有一张有两张纸的工作簿。（角色和角色更新）。角色更新表包含角色更改和新角色。它有比Roles表更多的条目，但我只想处理Roles和RoleUpdates中的条目。 RoleUpdates中的一个列称为“status”，其中一个选项是roleChange。角色表是我想更新的具有较旧角色的表。基本上我想查看RoleUpdates中的status列，如果是“roleChange”，那么我想在RoleUpdates表中复制该角色列，并根据名称将其粘贴到Roles表中。例;

roleUpdates Sheet

COL A       COL B     COL C 
ROLES       NAMES     STATUS   
Teller      David     roleChange   
Customer    Tom       noChange 
Admin      Liam      roleChange
-------------


    Before Running Macro
Roles Sheet
    ------------- 
COL A       COL B 
ROLES       NAMES 
Customer    Liam 
Admin       David  
Teller      Tom
    -------------


After Running Macro
Roles Sheet
    ------------- 
COL A       COL B 
ROLES       NAMES 
Admin       Liam 
Teller      David  
Customer    Tom
    -------------

I hope this was clear enough. Thanks in advance.

我希望这很清楚。提前致谢。

1 个解决方案

#1

This checks the status column, then if it's a rolechange, looks for the name on the other sheet and brings the new value in from the roleUpdates sheet.

这将检查状态列，然后如果它是rolechange，则在另一个工作表中查找名称并从roleUpdates工作表中提取新值。

Sub ChangesRoles()
Dim i As Long, j As Long, sht As Worksheet, lastrow As Long
Set sht = ThisWorkbook.Worksheets("roleUpdates")
lastrow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row

For i = 2 To lastrow
    If Worksheets("roleUpdates").Range("C" & i).Value = "roleChange" Then
        For j = 2 To lastrow
            If Worksheets("Roles Sheet").Range("B" & j).Value = Worksheets("roleUpdates").Range("B" & i).Value Then
                Worksheets("Roles Sheet").Range("A" & j).Value = Worksheets("roleUpdates").Range("A" & i).Value
            End If
        Next j
    End If
Next i

End Sub