取决于单元格值，Excel副本为特定表

Question

我目前正在尝试将文本从前纸复制到变体表。

我想要的是将Celly J19，J20，J21复制到不同片的细胞A1，B1，C1。J19决定要复制到每个 agent 有自己的表格，其宏将代理名称拉到数据验证中。

j19 =代理名称

J20 =假期开始日期

J21 =假期结束日期

我如何更改 Set wsDestin = Sheets("Agent1")，以便它查看j19来决定目标单元格。

Sub CopyColumnP()
    Dim wsSource As Worksheet
    Dim wsDestin As Worksheet
    Dim lngDestinRow As Long
    Dim rngSource As Range
    Dim rngCel As Range
    Set wsSource = Sheets("Front")
    Set wsDestin = Sheets("Agent1")
    With wsSource
        Set rngSource = .Range(.Cells(19, "J"), .Cells(.Rows.Count, "J").End(xlUp))
    End With
    For Each rngCel In rngSource
        If rngCel.Value = "Design" Then
            With wsDestin
                lngDestinRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row
                rngCel.EntireRow.Copy Destination:=wsDestin.Cells(lngDestinRow, "A")
            End With
        End If
    Next rngCel
    End Sub

Answer 1

很容易：

Set wsDestin = ThisWorkbook.Worksheets(wsSource.Range("J19").Value)

检查表是否存在结束是否阻止错误使用：

On Error Resume Next
    Set wsDestin = ThisWorkbook.Worksheets(wsSource.Range("J19").Value)
    If Not Err.Number = 0 Then 
        MsgBox "Sheet '" & wsSource.Range("A1").Value & "' not found."
        Exit Sub
    End If
On Error GoTo 0

---

或J19不包含目标工作表名称，但您需要根据J19的值选择一个工作表，然后使用：

Select Case wsSource.Range("J19").Value
    Case "AAA" 'if value of J19 is AAA select sheet A
        Set wsDestin = Sheets("A")
    Case "B", "C" 'if value of J19 is B or C select sheet BC
        Set wsDestin = Sheets("BC")
    Case Else
        MsgBox "no corresponding worksheet found."
End Select