VBA复制粘贴错误

Question

这是一个很小的错误，但我想不出来。我有一个vba脚本，它正在复制和粘贴几个项目。它适用于除最后一种以外的一切。错误是

无法粘贴信息，因为复制区域和粘贴区域大小和形状不一样。尝试以下方法之一：

1. 单击单个单元格，然后粘贴。·
2. 选择大小和形状相同的矩形，然后粘贴。

以下是代码：

Sub MakePropertyPage(propertyNum, aFile)
Dim sheetName As String
sheetName = "Prop " & propertyNum

'OPENS INDIVIDUAL WORKBOOKS TO PULL IN INPUT INFORMATION
Workbooks.Open Filename:=vpath & ThisWorkbook.Path & "\" & aFile & ".xlsb",                 UpdateLinks:=False, ReadOnly:=True

Dim sourceRangeAsString As String, dest As Range, clearArea As Range
Dim sheetToEdit As Worksheet
Set sheetToEdit = ThisWorkbook.Sheets(sheetName)

Set clearArea = sheetToEdit.Range("AL100:CC900")
clearArea.Delete

sourceRangeAsString = "RentRoll"
Set dest = sheetToEdit.Range("AL100")
Call CopyAndPaste(Workbooks(aFile & ".xlsb"), sourceRangeAsString, dest)

sourceRangeAsString = "Underwriting"
Set dest = sheetToEdit.Range("AL300")
Call CopyAndPaste(Workbooks(aFile & ".xlsb"), sourceRangeAsString, dest)

sourceRangeAsString = "Projections"
Set dest = sheetToEdit.Range("AL400")
Call CopyAndPaste(Workbooks(aFile & ".xlsb"), sourceRangeAsString, dest)

sourceRangeAsString = "RolloverCalculations"
dest = sheetToEdit.Range("AL500")
Call CopyAndPaste(Workbooks(aFile & ".xlsb"), sourceRangeAsString, dest)



'Close the workbook now that we are done with it
Workbooks(aFile & ".xlsb").Close savechanges:=False
End Sub

Sub CopyAndPaste(sourceFile, sourceRangeAsString, dest)
    Set source = sourceFile.Names(sourceRangeAsString).RefersToRange
    source.Copy
    dest.PasteSpecial (xlPasteValues)
    source.Copy
    dest.PasteSpecial (xlPasteFormats)
End Sub

Answer 1

你错过了一个Set：

dest = sheetToEdit.Range("AL500")