“复制”方法上的运行时错误

Question

在复制方法上我得到了一个运行时错误。

错误信息是：

“运行时错误'-2147417848 (80010108)'：对象‘范围’的方法‘复制’失败”

错误指向下面的代码行：

WST.Cells.Copy Destination:=WSD.Cells(1，1)

这是工作簿中所有的vba。

Option Explicit
Sub CreateReport()
Dim WSD As Worksheet
Dim WST As Worksheet
Dim ST As Long
Dim LastRow As Long
Dim LastRow1 As Long
Dim LastCol As Long
Dim FinalRow As Long
Dim i As Long


ST = 7
Set WSD = ActiveSheet
FinalRow = 42
Set WST = Sheets("BIF BR Prospects-Per Mgmt")
LastRow = WST.Cells(Rows.Count, 1).End(xlUp).Row
If LastRow > 1 Then
 WST.Cells(2, 1).Resize(LastRow - 1, 13).Copy
 WSD.Cells(FinalRow, 1).Insert Shift:=xlDown

 WSD.Cells(FinalRow, 13).Resize(LastRow - 1, 1).FormulaR1C1 = "=RC[-8]& ""/"" & RC[-2]"
 WSD.Cells(FinalRow, 5).Resize(LastRow - 1, 1).Value = WSD.Cells(FinalRow, 13).Resize(LastRow - 1, 1).Value
 WSD.Cells(FinalRow, 11).Resize(LastRow - 1, 1).ClearContents
 WSD.Cells(FinalRow, 13).Resize(LastRow - 1, 1).ClearContents

 WSD.Cells(FinalRow, 14).Resize(LastRow - 1, 1).FormulaR1C1 = "=IF(OR(RC[-5]="""",RC[-2]=""""),"""",RC[-5] & "" "" & RC[-2])"
 WSD.Cells(FinalRow, 9).Resize(LastRow - 1, 1).Value = WSD.Cells(FinalRow, 14).Resize(LastRow - 1, 1).Value
 WSD.Cells(FinalRow, 12).Resize(LastRow - 1, 1).ClearContents
 WSD.Cells(FinalRow, 14).Resize(LastRow - 1, 1).ClearContents


 WSD.Rows(FinalRow + LastRow - 1).Copy
 WSD.Rows(FinalRow & ":" & FinalRow + LastRow - 1).PasteSpecial Paste:=xlPasteFormats

 WSD.Cells(FinalRow + LastRow, 2).FormulaR1C1 = "=COUNTA(R[" & -LastRow & "]C[-1]:R[-2]C[-1])"
 WSD.Cells(FinalRow + LastRow, 5).FormulaR1C1 = "=SUM(R[" & -LastRow & "]C[5]:R[-2]C[5])"
 WSD.Rows(FinalRow + LastRow - 1).Delete

 WSD.Range(Cells(FinalRow - 1, 1), Cells(FinalRow + LastRow - 2, 10)).Select
 WSD.Sort.SortFields.Clear
 WSD.Sort.SortFields.Add Key:=Range(Cells(FinalRow, 6), Cells(FinalRow + LastRow - 2, 6)), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
 WSD.Sort.SortFields.Add Key:=Range(Cells(FinalRow, 4), Cells(FinalRow + LastRow - 2, 4)), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
 With WSD.Sort
    .SetRange Range(Cells(FinalRow - 1, 1), Cells(FinalRow + LastRow - 2, 10))
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .Apply
 End With

End If

FinalRow = FinalRow - ST
Set WST = Sheets("Funnel Status-Per AE info")
LastRow = WST.Cells(Rows.Count, 1).End(xlUp).Row
LastCol = WST.Cells(1, Columns.Count).End(xlToLeft).Column + 2
If LastRow > 1 Then
 Range(WST.Cells(1, 1), WST.Cells(LastRow, 1)).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range(WST.Cells(1, LastCol), WST.Cells(1, LastCol)), Unique:=True
 LastRow1 = WST.Cells(Rows.Count, LastCol).End(xlUp).Row
 WST.Cells(1, LastCol + 1) = 1
 WST.Cells(1, LastCol + 2) = 2
 WST.Cells(1, LastCol + 3) = 3
 WST.Cells(1, LastCol + 4) = 4
 WST.Cells(1, LastCol + 5) = 5

 WST.Cells(2, LastCol + 1).Resize(LastRow1 - 1, 5).FormulaR1C1 = "=COUNTIFS(C1,RC4,C2,R1C)"
 WST.Cells(2, LastCol + 1).Resize(LastRow1 - 1, 5).Value = WST.Cells(2, LastCol + 1).Resize(LastRow1 - 1, 5).Value
 WST.Cells(2, LastCol).Resize(LastRow1 - 1, 11).Copy
 WSD.Cells(FinalRow, 1).Insert Shift:=xlDown

 WSD.Rows(FinalRow + LastRow1 - 1).Copy
 WSD.Rows(FinalRow & ":" & FinalRow + LastRow1 - 1).PasteSpecial Paste:=xlPasteFormats

 Range(WST.Cells(1, LastCol), WST.Cells(LastRow1, LastCol + 5)).ClearContents
 WSD.Cells(FinalRow, 7).Resize(LastRow1 + 1, 1).FormulaR1C1 = "=SUM(RC[-5]:RC[-1])"
 WSD.Cells(FinalRow + LastRow1 - 1, 7) = ""
 WSD.Cells(FinalRow + LastRow1, 2).Resize(1, 5).FormulaR1C1 = "=SUM(R[" & -LastRow1 & "]C:R[-2]C)"
 WSD.Rows(FinalRow + LastRow1 - 1).Delete
End If

FinalRow = FinalRow - ST
Set WST = Sheets("BR Opened this week")
LastRow = WST.Cells(Rows.Count, 1).End(xlUp).Row
If LastRow > 1 Then
 WST.Cells(2, 1).Resize(LastRow - 1, 12).Copy
 WSD.Cells(FinalRow, 1).Insert Shift:=xlDown

 WSD.Cells(FinalRow, 12).Resize(LastRow - 1, 1).FormulaR1C1 = "=RC[-7]& ""/"" & RC[-2]"
 WSD.Cells(FinalRow, 5).Resize(LastRow - 1, 1).Value = WSD.Cells(FinalRow, 12).Resize(LastRow - 1, 1).Value
 WSD.Cells(FinalRow, 10).Resize(LastRow - 1, 1).ClearContents
 WSD.Cells(FinalRow, 12).Resize(LastRow - 1, 1).ClearContents

 WSD.Cells(FinalRow, 13).Resize(LastRow - 1, 1).FormulaR1C1 = "=IF(OR(RC[-6]="""",RC[-2]=""""),"""",RC[-6] & "" "" & RC[-2])"
 WSD.Cells(FinalRow, 7).Resize(LastRow - 1, 1).Value = WSD.Cells(FinalRow, 13).Resize(LastRow - 1, 1).Value
 WSD.Cells(FinalRow, 11).Resize(LastRow - 1, 1).ClearContents
 WSD.Cells(FinalRow, 13).Resize(LastRow - 1, 1).ClearContents



 WSD.Rows(FinalRow + LastRow - 1).Copy
 WSD.Rows(FinalRow & ":" & FinalRow + LastRow - 1).PasteSpecial Paste:=xlPasteFormats

 WSD.Cells(FinalRow + LastRow, 4).FormulaR1C1 = "=SUM(R[" & -LastRow & "]C[5]:R[-2]C[5])"
 WSD.Rows(FinalRow + LastRow - 1).Delete
End If

FinalRow = FinalRow - ST
Set WST = Sheets("REMOVED from BIF this week")
LastRow = WST.Cells(Rows.Count, 1).End(xlUp).Row
If LastRow > 1 Then
 WST.Cells(2, 1).Resize(LastRow - 1, 11).Copy
 WSD.Cells(FinalRow, 1).Insert Shift:=xlDown

 WSD.Cells(FinalRow, 13).Resize(LastRow - 1, 1).FormulaR1C1 = "=RC[-8]& ""/"" & RC[-2]"
 WSD.Cells(FinalRow, 5).Resize(LastRow - 1, 1).Value = WSD.Cells(FinalRow, 13).Resize(LastRow - 1, 1).Value
 WSD.Cells(FinalRow, 11).Resize(LastRow - 1, 1).ClearContents
 WSD.Cells(FinalRow, 13).Resize(LastRow - 1, 1).ClearContents


 WSD.Rows(FinalRow + LastRow - 1).Copy
 WSD.Rows(FinalRow & ":" & FinalRow + LastRow - 1).PasteSpecial Paste:=xlPasteFormats

 WSD.Cells(FinalRow + LastRow, 4).FormulaR1C1 = "=SUM(R[" & -LastRow & "]C[6]:R[-2]C[6])"
 WSD.Rows(FinalRow + LastRow - 1).Delete
End If

FinalRow = FinalRow - ST
Set WST = Sheets("BR Backlog of conf-unopened")
LastRow = WST.Cells(Rows.Count, 1).End(xlUp).Row
If LastRow > 1 Then
 WST.Cells(2, 1).Resize(LastRow - 1, 12).Copy
 WSD.Cells(FinalRow, 1).Insert Shift:=xlDown

 WSD.Cells(FinalRow, 12).Resize(LastRow - 1, 1).FormulaR1C1 = "=RC[-7]& ""/"" & RC[-2]"
 WSD.Cells(FinalRow, 5).Resize(LastRow - 1, 1).Value = WSD.Cells(FinalRow, 12).Resize(LastRow - 1, 1).Value
 WSD.Cells(FinalRow, 10).Resize(LastRow - 1, 1).ClearContents
 WSD.Cells(FinalRow, 12).Resize(LastRow - 1, 1).ClearContents

 WSD.Cells(FinalRow, 13).Resize(LastRow - 1, 1).FormulaR1C1 = "=IF(OR(RC[-6]="""",RC[-2]=""""),"""",RC[-6] & "" "" & RC[-2])"
 WSD.Cells(FinalRow, 7).Resize(LastRow - 1, 1).Value = WSD.Cells(FinalRow, 13).Resize(LastRow - 1, 1).Value
 WSD.Cells(FinalRow, 11).Resize(LastRow - 1, 1).ClearContents
 WSD.Cells(FinalRow, 13).Resize(LastRow - 1, 1).ClearContents




 WSD.Rows(FinalRow + LastRow - 1).Copy
 WSD.Rows(FinalRow & ":" & FinalRow + LastRow - 1).PasteSpecial Paste:=xlPasteFormats

 WSD.Cells(FinalRow + LastRow, 4).FormulaR1C1 = "=SUM(R[" & -LastRow & "]C[5]:R[-2]C[5])"
 WSD.Rows(FinalRow + LastRow - 1).Delete
End If

FinalRow = FinalRow - ST
Set WST = Sheets("BR Set-Confirmed this week")
LastRow = WST.Cells(Rows.Count, 1).End(xlUp).Row
If LastRow > 1 Then
 WST.Cells(2, 1).Resize(LastRow - 1, 12).Copy
 WSD.Cells(FinalRow, 1).Insert Shift:=xlDown

 WSD.Cells(FinalRow, 12).Resize(LastRow - 1, 1).FormulaR1C1 = "=RC[-7]& ""/"" & RC[-2]"
 WSD.Cells(FinalRow, 5).Resize(LastRow - 1, 1).Value = WSD.Cells(FinalRow, 12).Resize(LastRow - 1, 1).Value
 WSD.Cells(FinalRow, 10).Resize(LastRow - 1, 1).ClearContents
 WSD.Cells(FinalRow, 12).Resize(LastRow - 1, 1).ClearContents

 WSD.Cells(FinalRow, 13).Resize(LastRow - 1, 1).FormulaR1C1 = "=IF(OR(RC[-6]="""",RC[-2]=""""),"""",RC[-6] & "" "" & RC[-2])"
 WSD.Cells(FinalRow, 7).Resize(LastRow - 1, 1).Value = WSD.Cells(FinalRow, 13).Resize(LastRow - 1, 1).Value
 WSD.Cells(FinalRow, 11).Resize(LastRow - 1, 1).ClearContents
 WSD.Cells(FinalRow, 13).Resize(LastRow - 1, 1).ClearContents

 WSD.Rows(FinalRow + LastRow - 1).Copy
 WSD.Rows(FinalRow & ":" & FinalRow + LastRow - 1).PasteSpecial Paste:=xlPasteFormats

 WSD.Cells(FinalRow + LastRow, 4).FormulaR1C1 = "=SUM(R[" & -LastRow & "]C[5]:R[-2]C[5])"
 WSD.Rows(FinalRow + LastRow - 1).Delete

End If
Cells.EntireRow.AutoFit
WSD.Range(Columns(11).Address & ":" & Columns(Columns.Count).Address).ClearContents
WSD.Cells(1, 1).Select

End Sub
Sub ClearReport()

 Dim WSD As Worksheet
 Dim WST As Worksheet
 Set WSD = ActiveSheet
 Set WST = Sheets("GM")
 WST.Cells.Copy Destination:=WSD.Cells(1, 1)
End Sub
Sub Printing()

    With ActiveSheet.PageSetup
      .Orientation = xlLandscape
      .PaperSize = xlPaperLetter
      .Zoom = False
      .FitToPagesWide = 1
      .FitToPagesTall = False
    End With
End Sub

Answer 1

我不认为您的代码有问题，Google搜索显示了各种不同的信息/环境，这些错误都会弹出。来自Windows Wiki页面

"Excel运行时错误80010108错误代码是由windows操作系统中配置错误的系统文件以某种方式造成的。“

这就解释了为什么当其他人尝试相同的代码时，它对其他人有效。该站点上列出了一个fix实用程序。

Answer 2

同样的问题，对我来说，由于Excel更新KB2956081。问题不是在宏中，而是在Excel中复制/过去的操作。如果整个工作表(所有单元格都被选中)被复制并经过另一个工作表。(或过去的特殊情况)，因此Excel正在崩溃。(但如果选择不是所有单元格，则Dos不会崩溃)。包括这样的命令make的宏生成自动化错误。如果删除了更新，请再次右转Excel。