excel - 收到错误 1004：我们可以对合并的单元格执行此操作.. 为什么？

Question

收到错误 1004： 我们可以对合并的单元格执行此操作..

为什么 ？

我该如何解决？

我想将twb中的单元格复制到extwb ...

这段代码就像 = 运行 -> 出错 -> 重置宏 -> 再次运行 -> 它可以工作

Sub Ke1()

Dim twb As Workbook
Dim extwb As Workbook
Dim rng As Range
Dim i As Long
Dim lastRow As Long
lastRow = Cells(Rows.Count, 1).End(xlUp).Row + 1


Set twb = Workbooks.Open("C:\Users\faisal.abraham\Documents\Faisal\Travel\CCCPU030732017.xlsx")
Set extwb = Workbooks.Open("C:\Users\faisal.abraham\Documents\Faisal\Travel\CCCPU030732018.xlsx")
'Set twb = Application.Workbooks("CCCPU030732017.xlsx")
'Set extwb = Application.Workbooks("CCCPU030732018.xlsx")

Set rng = twb.Worksheets("PAID").Range("A1:E500")
For i = 5 To rng.Cells(Rows.Count, 2).End(xlUp).Row

    rng.Cells(i, 1).Copy extwb.Worksheets("PAID").Cells(lastRow, 1)
    rng.Cells(i, 2).Copy extwb.Worksheets("PAID").Cells(lastRow, 2)
    rng.Cells(i, 3).Copy extwb.Worksheets("PAID").Cells(lastRow, 3)
    rng.Cells(i, 4).Copy extwb.Worksheets("PAID").Cells(lastRow, 4)
    rng.Cells(i, 5).Copy extwb.Worksheets("PAID").Cells(lastRow, 5)
      
    lastRow = lastRow + 1
    
Next i

twb.Close savechanges:=False
extwb.Saved = True

End Sub

标签： excelvba

Answer 1

您已合并extwb.Worksheets("PAID")要粘贴的单元格。

那是行不通的 -.Copy源需要具有相同的大小，即如果两个单元格在目标范围内合并，则您需要在源范围内有两个合并的单元格。

您可以通过以下方式解决此问题：

extwb.Worksheets("PAID").Range("A" & lastRow & ":E" & lastrow).Unmerge
rng.Cells(i, 1).Copy extwb.Worksheets("PAID").Cells(lastRow, 1)
rng.Cells(i, 2).Copy extwb.Worksheets("PAID").Cells(lastRow, 2)
rng.Cells(i, 3).Copy extwb.Worksheets("PAID").Cells(lastRow, 3)
rng.Cells(i, 4).Copy extwb.Worksheets("PAID").Cells(lastRow, 4)
rng.Cells(i, 5).Copy extwb.Worksheets("PAID").Cells(lastRow, 5)