excel - 使用动态范围将一张纸复制到多张纸的宏

Question

各位 VBA 奇才们，

使用以下代码，我能够生成多个标签名称等于动态范围的工作表:

Sub SheetMacro()
    Dim MyCell As Range, MyRange As Range

    Set MyRange = Sheets("Control_Sheet").Range("F7")
    Set MyRange = Range(MyRange, MyRange.End(xlDown))

    For Each MyCell In MyRange
        Sheets.Add After:=Sheets(Sheets.Count) 'creates a new worksheet
        Sheets(Sheets.Count).Name = MyCell.Value ' renames the new worksheet
    Next MyCell
End Sub

在尝试将带有公式的源模板(EAC 摘要)复制到所有最近创建的工作表中时，我遇到了一些障碍。添加此代码会复制 EAC 摘要，但不会命名动态范围内的选项卡:

Sub SheetMacro()
    Dim MyCell As Range, MyRange As Range
    Dim ws1 As Worksheet

    Set MyRange = Sheets("Control_Sheet").Range("F7")
    Set MyRange = Range(MyRange, MyRange.End(xlDown))

    For Each MyCell In MyRange
        Sheets.Add After:=Sheets(Sheets.Count) 'creates a new worksheet
        Sheets(Sheets.Count).Name = MyCell.Value ' renames the new worksheet

        Set ws1 = ThisWorkbook.Worksheets("EAC Summary")
        ws1.Copy ThisWorkbook.Sheets(Sheets.Count)

    Next MyCell  

End Sub

是否有一种最佳方法来组合代码以复制源模板并根据动态范围中的输入命名选项卡？

提前致谢。

Answer 1

我能够使用替代命令进行复制，这解决了我最初的问题。这是代码，适用于任何可能遇到此问题的人:

Sub SheetMacro3()
Dim MyCell As Range, MyRange As Range

Set MyRange = Sheets("Control_Sheet").Range("F7")
Set MyRange = Range(MyRange, MyRange.End(xlDown))
Sheets("EAC Summary").Visible = True   'displays master template
For Each MyCell In MyRange

    Sheets("EAC Summary").Copy After:=Worksheets(Worksheets.Count)
    Sheets(Sheets.Count).Name = MyCell.Value ' renames the new worksheet
Next MyCell
Sheets("EAC Summary").Visible = False 'hides master template
End Sub