VBA Excel ERROR 1004 problems with Paste Value

Question

I am kind of new with VBA and I have a problem I cannot solve and cannot find the right solutions in earlier questions. In fact it looks quite simple. I want to copy a range in worksheet with the name "Blad3" and paste the values in in worksheet with the name "Blad1". This is what I made and where does it go wrong?

    ActiveSheet.Cells(dattel, 4).Select

    ActiveCell.Range("A1:J1").Copy

    Sheets("Blad1").Select
    Cells(8 + aantkk, 6).Select
    ActiveSheet.Unprotect
'    ActiveCell.PasteSpecial xlPasteValues
    Selection.PasteSpecial Paste:=xlValue
'    ActiveSheet.Paste

    ActiveSheet.Protect

Yes, please answer on what line it does go wrong and what error message you get — , Sep 30 '17 at 12:09
It would not be a surprise to learn that `Unprotect` ends the `CutCopyMode` so a paste would not work. The [real answer](https://meta.stackexchange.com/q/66377/147640) to your question though is [here](https://stackoverflow.com/a/29787935/11683). Having that done, you should really see https://stackoverflow.com/q/10714251/11683. — GSerg, Sep 30 '17 at 12:11

score 0 · Answer 1 · answered Sep 30 '17 at 12:26

0

Would this work for you?

Sheets("Blad3").Range("A1:J1").Copy

Sheets("Blad1").Range("A1:J1").PasteSpecial Paste:=xlValue

I tested it on a new workbook and it seemed to work just fine.

answered Sep 30 '17 at 12:26

spiralrain

16

score 0 · Accepted Answer · 2017-09-30T17:25:01.487

First, the real answer to your dilemma is to protect the worksheet with the UserInterfaceOnly:=True parameter so that you do not have to Unprotect it to write values using VBA code.

Run this once.

sub protectBlad1FromUser()
    worksheets("Blad1").unprotect
    worksheets("Blad1").protect UserInterfaceOnly:=True
end sub

Now you can do anything you want to the Blad1 worksheet in VBA while protecting it from the user.

As to your original code, it is confusing. If .Cells(dattel, 4) is D4 on the Blad3 worksheet then ActiveCell.Range("A1:J1").Copy doesn't copy A1:J1; it copies D4:M4. In any event, direct value transfer is a more efficient method of transferring values than Copy, Paste Special, Values.

dim rng as range
set rng = worksheets("blad3").cells(dattel, 4).resize(1, 10)  '<~~ figure out what this is supposed to be
with worksheets("Blad1")
    .cells(8 + aantkk, 6).resize(rng.rows.count, rng.columns.count) = rng.value
end with

Thanks for your answer. I have now ERROR 438 when I type this code — Chielmos, Sep 30 '17 at 17:11
My apologies; I should have compiled that before posting. I only wrote it off the top of my head; turns out I wrote off the bottom of a lower anatomy area. Change `rng.values` to `rng.value` as fixed above. — , Sep 30 '17 at 17:24

Mrig · Answer 3 · 2017-09-30T12:30:14.260

May be try this

Sub Demo()
    Dim srcSht As Worksheet, destSht As Worksheet
    Dim rng As Range
    Set srcSht = ThisWorkbook.Sheets("Blad3")   'this is source sheet
    Set destSht = ThisWorkbook.Sheets("Blad1")  'this is destination sheet

    With destSht
        .Unprotect                           'unprotect sheet Blad1
        Set rng = srcSht.Range("A1:J1")      'set range to copy
        .Cells(8 + aantkk, 6).Resize(rng.Rows.Count, rng.Columns.Count).Value = rng.Value 'paste only values
        .Protect                             'protect sheet Blad1
    End With
End Sub

SELECT and ACTIVATE should be avoided. See this for details.

VBA Excel ERROR 1004 problems with Paste Value

3 Answers3