VBA错误范围类的PasteSpecial方法失败

Question

我一直都有这个错误，尽管我尝试删除选择等。在此之前，我更新了一些单元格，然后代码复制并选择了7行(应该选择10行(，在粘贴之前给出了错误。代码被卡住的地方是

Range("C4:AN" & Range("B5").Value).PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

请帮助

Sub Refresh()
Call Confirm
Dim F As New FDSOfficeAPI_Server
Application.Iteration = True
Application.ScreenUpdating = False
Sheets("Supply Chain").Select
Range("C1:BA10000").Select
V = F.RefreshSelectedFDSCodes
Range("B1").Select
Application.ScreenUpdating = True
Sheets("Supply Chain").Select
Range("C4:AN4").Copy
Range("C4:AN" & Range("B5").Value).PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("BD4:CO4").Select
Application.CutCopyMode = False
Selection.Copy
Range("BD4:CO" & Range("BC5").Value).Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("DE4:EO4").Select
Application.CutCopyMode = False
Selection.Copy
Range("DE4:EO" & Range("DD5").Value).Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

End Sub

Answer 1

如果将重复的部分移动到一个单独的sub，您的代码将更容易维护。尝试类似的方法：

Sub Tester()
FillDownFormats Range("C4:AN4"), Range("B5").Value
FillDownFormats Range("BD4:CO4"), Range("BC5").Value
FillDownFormats Range("DE4:EO4"), Range("DD5").Value
End Sub
'Fill down the formats from rngSource to fill total of 'numRows' rows
Sub FillDownFormats(rngSource As Range, numRows As Long)

rngSource.Offset(1, 0).Resize(numRows-1).ClearFormats 'clear any existing formatting
rngSource.Copy
rngSource.Resize(numRows).PasteSpecial _
Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
End Sub