CODEWITHSUNDEEP
excelvba
mohamed123
mohamed123

Reputation: 51

VBA error PasteSpecial Method of Range Class failed

I have this error all the time, even though I tried removing selection etc. Before this I update some cells and then the code copies and selects 7 rows (it was supposed to select 10) and before pasting gives the error. Where the code gets stuck is

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

Please help

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

Upvotes: 0

Views: 175

Answers (1)

Tim Williams
Tim Williams

Reputation: 166790

Your code would be easier to maintain if you move the repeated parts to a separate sub. Try something more like this:

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

Upvotes: 1

Related Questions