runtime error 404 pastespecial method of range class failed

Question

When I us unprotect in vba only then I am getting this error, Do not know why, if I remove code to unprotect sheet and also unprotected the sheet then no error.

code

Sub EXPORT()
        Worksheets("DASHBOARD").Range("A6:I50").copy

        Workbooks.Open ActiveWorkbook.Path & "\BOQ.XLSX"
        'Workbooks.Open "K:\Desktop Files\PAS\ISCM\BOQ\BOQ.XLSX"
        'Worksheets("SHEET1").Range("B32").Select
        'Range("B32").End(xlUp).Offset(1, 0).Select
      Sheets("Sheet1").Unprotect Password:=1
        Worksheets("Sheet1").Range("B12").Select
        ActiveCell.PasteSpecial Paste:=xlPasteValues

      Sheets("Sheet1").Protect Password:=1
        ActiveWorkbook.Save
End Sub

Always put the copy action right one line before you paste. Otherwise any actions in between might cancel the copy action. Make sure you don't use `.Select` and `ActiveCell` see [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). Your paste should look like the `.Copy` line without using `.Select`. — Pᴇʜ, May 12 '20 at 07:53

FaneDuru · Answer 1 · 2020-05-12T08:41:51.067

0

Try, after opening of ActiveWorkbook.Path & "\BOQ.XLSX". Otherwise, the ClipBoard is cleaned when the workbook is open:

Worksheets("DASHBOARD").Range("A6:I50").Copy
Worksheets("Sheet1").Range("B12").PasteSpecial Paste:=xlPasteValues

Or, keep your way, but put the range to be copied in a variable:

Dim rng As Range
Set rng = Worksheets("DASHBOARD").Range("A6:I50")

 'open here the other workbook and then
  Workbooks.Open ActiveWorkbook.Path & "\BOQ.XLSX"
  Sheets("Sheet1").Unprotect Password:=1

 rng.Copy
 Worksheets("Sheet1").Range("B12").PasteSpecial Paste:=xlPasteValues

edited May 12 '20 at 08:41

answered May 12 '20 at 08:35

FaneDuru

38,298
4
19
27

Note that `Sheets("Sheet1").Unprotect` is not very reliable using `BOQ.XLSX`. When working with different workbooks you should always specify for every sheet in which workbook it is. So: `Set rng = ThisWorkbook.Worksheets("DASHBOARD").Range("A6:I50")` and `Dim OpenWb As Workbook: Set OpenWb = Workbooks.Open(ActiveWorkbook.Path & "\BOQ.XLSX"` (probaby better `ThisWorkbook` if it is not an add-in), and `OpenWb.Worksheets("Sheet1").Unprotect Password:=1` as well as `OpenWb.Worksheets("Sheet1").Range("B12").PasteSpecial` otherwise there is the possibility that it fails again. – Pᴇʜ May 12 '20 at 09:01
@Pᴇʜ: Of course it is better to fully define the sheets. I only kept his code and tried showing how to adapt it in order to work. In his code, coming immediately after the workbook opening, no confusions could happen. But, if he needs to use those variables in a later step, of course their fully definition is necessary. On the other hand, we cannot be sure that that `Worksheets("DASHBOARD")` is on the workbook keeping the code, or only belongs to the active workbook... Anyhow, your idea of full variables definition is more then welcomed. I always do so in my projects. – FaneDuru May 12 '20 at 09:12

runtime error 404 pastespecial method of range class failed

1 Answers1