Excel VBA在列中重复复制和粘贴一系列单元格

MDL 发表于 Dev

MDL

我以两种不同的方式尝试过。

首先，选择要复制的单元格范围，然后选择要粘贴的目标范围。下面是代码：

Sub PanelData()

Dim size As Integer
Dim i As Integer
Dim shrate As Worksheet
Dim shpanel As Worksheet


Set shrate = Sheets("Rate")
Set shpanel = Sheets("Panel")

size = shrate.Range("B4").End(xlDown).Row

shrate.Range(Cells(4, 2), Cells(size, 2)).Select
Selection.Copy

shpanel.Cells(1, 1).Value = size - 3

For i = 1 To 18

shpanel.Range(Cells(4, 1).Offset((i - 1) * (size - 3), 0), Cells(3, 1).Offset(i * (size - 3), 0)).Select
        Selection.PasteSpecial Paste:=xlPasteValues
        Selection.NumberFormat = "m/d/yyyy"

 Next i

End Sub

其次，复制该范围内的单元格，然后将其一一粘贴到目标列。这是代码：

Sub LoopingCP()
Dim size As Integer
Dim shrate As Worksheet
Dim shpanel As Worksheet

Set shrate = Sheets(2)
Set shpanel = Sheets(4)

size = shrate.Cells(4, 2).End(xlDown).Row - 3

For x = 1 To 18

    For i = 1 To size
        shrate.Cells(i + 3, 2).Select
            Selection.Copy

        shpanel.Cells(x * (i + 3), 1).Select
            Selection.PasteSpecial Paste:=xlPasteValues
            Selection.NumberFormat = "m/d/yyyy"

    Next i

Next x
End Sub

这些尝试都没有成功。我做错了什么？

谢谢

布鲁斯·韦恩

有几件事使它无法正常工作。首先，使用范围变量的时候，比如Range()，Cells()，Rows()，Columns()，等你应该始终是明确你希望运行的表。

其次，您要避免使用.Select，因为我已在评论中链接到。实际上，您只需“备份”以.Select/开头并以/开头的两行.Selection

看看是否可行：

Sub PanelData()

Dim size As Integer
Dim i As Integer
Dim shrate As Worksheet
Dim shpanel As Worksheet


Set shrate = Sheets("Rate")
Set shpanel = Sheets("Panel")

size = shrate.Range("B4").End(xlDown).Row

shrate.Range(shrate.Cells(4, 2), shrate.Cells(size, 2)).Copy

shpanel.Cells(1, 1).Value = size - 3

For i = 1 To 18
    With shpanel.Range(shpanel.Cells(4, 1).Offset((i - 1) * (size - 3), 0), shpanel.Cells(3, 1).Offset(i * (size - 3), 0))
        .PasteSpecial Paste:=xlPasteValues
        .NumberFormat = "m/d/yyyy"
    End With
 Next i

End Sub

Sub LoopingCP()
Dim size As Integer
Dim shrate As Worksheet
Dim shpanel As Worksheet

Set shrate = Sheets(2)
Set shpanel = Sheets(4)

size = shrate.Cells(4, 2).End(xlDown).Row - 3

For x = 1 To 18
    For i = 1 To size
        shrate.Cells(i + 3, 2).Copy
        With shpanel.Cells(x * (i + 3), 1)
            .PasteSpecial Paste:=xlPasteValues
            .NumberFormat = "m/d/yyyy"
        End With
    Next i
Next x
End Sub

仅供参考-当您只需要这些值时，可以将范围的值设置为彼此相等，而不是使用.Copy/ .Paste，这样可以避免使用剪贴板，并在运行宏时节省了一些时间：

Range([destination range]).Value = Range([copy from range]).Value（当然，请注意，您需要在之前添加工作表名称Range()。

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。