excel - 如何将范围从工作表复制到其他工作表？

Question

我正在尝试创建一个将特定范围(CA1:CZ99)从“Sheet A”复制到许多其他工作表的宏。其他工作表的名称基于“工作表 B”中 F 列的值。
复制数据的代码很容易找到。

Worksheets("Sheet A").Range("CA1:CZ99").Copy Worksheets("Sheet X").Range("CA1")

但是如何将这部分循环到 F 列的所有工作表上？

Answer 1

将范围复制到多个工作表

Option Explicit

Sub CopyRange()
    
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    
    ' Source
    Dim sws As Worksheet: Set sws = wb.Worksheets("Sheet A")
    Dim srg As Range: Set srg = sws.Range("CA1:CZ99")
    
    ' Lookup
    Dim lws As Worksheet: Set lws = wb.Worksheets("Sheet B")
    Dim lfRow As Long: lfRow = 2
    Dim llRow As Long: llRow = lws.Cells(lws.Rows.Count, "F").End(xlUp).Row
    If llRow < lfRow Then Exit Sub ' no data
    Dim lrg As Range: Set lrg = lws.Cells(lfRow, "F").Resize(llRow - lfRow + 1)

    ' Copy to Destination
    Dim dws As Worksheet
    Dim lCell As Range
    Dim lCount As Long
    For Each lCell In lrg.Cells
        On Error Resume Next ' check if the worksheet exists
            Set dws = wb.Worksheets(CStr(lCell.Value))
        On Error GoTo 0
        If Not dws Is Nothing Then ' the worksheet exists
            lCount = lCount + 1
            srg.Copy dws.Range("CA1")
            Set dws = Nothing
        'Else ' the worksheet doesn't exist
        End If
    Next lCell
    
    ' Inform
    MsgBox "Range copied to " & lCount & " worksheets.", _
        vbInformation, "CopyRange"

End Sub