Paste Special error 1004 PasteSpecial method of Range class failed

Try the code below (explanation inside the code as comments):

Dim wk As Workbook
Set wk = Workbooks.Add

wk.SaveAs Filename:=ThisWorkbook.Path & "\" & "Budget.xlsx"
wk.Activate
wk.Unprotect

' have the Copy>>Paste section together
Dim LastCol As Long
Dim LastRow As Long

' you never mentioned which sheet to copy from, I used the first index
With ThisWorkbook.Sheets(1)
    LastCol = .Range("B3").End(xlToRight).Column
    LastRow = .Range("B3").End(xlDown).Row
    .Range("B3", .Cells(LastRow, LastCol)).Copy ' <-- Copy without Select
End With

' Paste without Select
wk.Worksheets("Sheet1").Range("B3").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False

Leave a Comment