我正在嘗試創建一個將特定范圍（CA1：CZ99）從“Sheet A”復制到許多其他作業表的宏。其他作業表的名稱基于“作業表 B”中 F 列的值。
復制資料的代碼很容易找到。
Worksheets("Sheet A").Range("CA1:CZ99").Copy Worksheets("Sheet X").Range("CA1")
但是如何將這部分回圈到 F 列的所有作業表上？
uj5u.com熱心網友回復：
將范圍復制到多個作業表
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
uj5u.com熱心網友回復：
準確指定從何處獲取資料作為變數，然后回圈遍歷它。例子：
Sub loopCopy()
Dim shtRng As Range
Dim c As Variant
Set shtRng = Worksheets("Sheet B").Range("F1:F5")
For Each c In shtRng
Worksheets("Sheet A").Range("CA1:CZ99").Copy Worksheets(c.Value).Range("CA1")
Next c
End Sub
這是一個非常基本的設定。如果列中的值與作業表不匹配，或者如果“作業表 A”或“作業表 B”更改名稱，它將崩潰。
您可能希望通過查找最后一行等來動態調整串列的大小。
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/405364.html
標籤：
上一篇：根據用戶輸入粘貼位置