vba - 复制工作表，根据范围重命名工作表，并根据范围编辑复制的工作表

Question

基本上我的目标是:

根据模板创建副本并根据名称列表重命名它们(我弄清楚了这部分)

根据另一个单元格范围更改副本中的两个单元格

我的代码

 Sub CopyInfoSheetandInsert()
 Dim rcell As Range
 Dim hcell As Range
 Dim Info As Worksheet
 Set Info = ActiveSheet
 For Each rcell In Range("B2:B30")
     If rcell.Value <> "" Then
           Sheets("Team Member (2)").Copy After:=Sheets("Info")
           Sheets("Team Member (3)").Name = rcell.Value
     End If
 Next rcell
 End Sub

我遇到的问题是在哪里添加我的第二个范围以及如何根据它更改单元格。

Answer 1

当您将"template"工作表复制到 之后的位置时信息 工作表，您立即知道它的 Worksheet.Index property将比 .Index 大一的信息工作表。在 With ... End With statement 中使用它识别新工作表并在那里执行所有操作。

Sub CopyInfoSheetandInsert()
    Dim rcell As Range, hcell As Range, Info As Worksheet

    Set Info = ActiveSheet

    For Each rcell In Info.Range("B2:B30")
        If rcell.Value <> "" Then
            Sheets("Team Member (2)").Copy After:=Sheets("Info")
            With Sheets(Sheets("Info").Index + 1)
                .Name = rcell.Value
                .Range("A1") = "this is the new worksheet"  '<~~perform the other operations like this
                .Range("A2") = Info.Range("Z99").Value  '<~~cannot reference Info as ActiveSheet anymore. Need direct worksheet reference like this.
                .Range("A3") = rcell.Offset(0, 1).Value '<~~you can still use rcell as a reference point for other information
            End With
        End If
    Next rcell
End Sub

或者，一旦新工作表被命名，您可以使用工作表 .Name property在循环到新的 rcell 之前.

    Set Info = ActiveSheet
    For Each rcell In Info.Range("B2:B30")
        If rcell.Value <> "" Then
            Sheets("Team Member (2)").Copy After:=Sheets("Info")
            Sheets("Team Member (3)").Name = rcell.Value
            With Sheets(rcell.Value)
                .Range("A1") = "this is the new worksheet"  '<~~perform the other operations like this
                .Range("A2") = Info.Range("Z99").Value  '<~~cannot reference Info as ActiveSheet anymore. Need direct worksheet reference like this.
                .Range("A3") = rcell.Offset(0, 1).Value '<~~you can still use rcell as a reference point for other information
            End With
        End If
    Next rcell

请记住，当前 ActiveSheet property复制时将更改为新工作表。如果您从 Info 工作表(原始 ActiveSheet)中检索信息，那么您需要使用工作表对象引用您 Set在复制过程开始之前。由于循环在 For ... Next 时建立了 Info 工作表上的单元格范围。开始，您仍然可以使用 rcell作为信息工作表上的引用点。