复制并粘贴到变量工作表和区域

Question

我想将固定工作表("c4:c178&"(中的一个区域复制并粘贴到可变工作表中。

我有两个下拉列表，一个是所有工作表名称的列表，另一个是列号。

我希望用户可以在下拉列表中选择工作表名称和列引用，然后单击宏按钮将区域复制并粘贴到该引用。

Sub CopyPaste()
Dim Sheetname As String
Sheetname = ActiveSheet.Range("i3").Value
Dim Col As Long
Col = ActiveSheet.Range("i4").Value
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Tracking Only")
Dim rng As Range
Set rng = ActiveSheet.Range("c4:C178")
With rng
ws.Cells(4, Col).Resize(.Rows.Count, .Columns.Count).Value = .Value
End With
End Sub

我收到

"运行时错误"1004"：应用程序定义或对象定义错误"；

它突出显示ws.cells代码。

Answer 1

将流程分解为多个步骤，使用变量存储下拉值，并将源范围的.Value分配给目标范围。

With ThisWorkbook.Worksheets("Tracking Only")
Dim sheetName As String
sheetName = .Range("I3").Value 
Dim col As Long
col = .Range("I4").Value

Dim rng As Range
Set rng = .Range("C4:C178")
End With
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets(sheetName)
With rng
ws.Cells(4, col).Resize(.Rows.Count, .Columns.Count).Value = .Value
End With