VB Run-time error

Hi Ryydo,

I have been trying to write a few VB codes for click buttons but when I run them I am getting a Run-time error '1004': Select method Range class failed

dialog error box. 

Any ideas to why this is occuring?

Private Sub CommandButton2_Click()

'

' year End

'

    Sheets("Sheet1").Select

    Range("A19").Select (This is the line highlighted during debugging).

    Selection.Copy

    Range("A5").Select

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

        :=False, Transpose:=False

End Sub

Your problem is due to the fact that you have not qualified the two ranges of interest. In the absence of any qualification, the expressions Range("A19")  and Range("A5")  are interpreted as applying to the sheet in which the code is found. As your code selects another sheet, it is not possible to select ranges on the original sheet and, as you have discovered, VBA will throw a runtime error.

You could overcome this problem by qualifying the two ranges thus:                      

'=========>>

Private Sub CommandButton2_Click()

    With Sheets("Sheet1")

        .Select

        .Range("A19").Select

        Selection.Copy

        .Range("A5").Select

        Selection.PasteSpecial Paste:=xlPasteValues, _

                               Operation:=xlNone, _

                               SkipBlanks:=False, _

                               Transpose:=False

    End With

End Sub

'<<=========

[Note the points which precede the two ranges inside the With - End With structure]

That said, however, it is rarely necessary, efficient or desirable to select range objects. Therefore, I would suggest instead the following adaptation of your code which qualifies the range objects but avoids any selections:

'=========>>

Private Sub CommandButton2_Click()

    With Sheets("Sheet1")

        .Range("A5") = .Range("A19").Value

    End With

End Sub

'<<=========

===

Regards,

Norman

This still fails on the same run time error "paste special method of range class failed" 2nd time around.

Then this issue is not an issue of the code, it's in the layout of your file (merged cells... whatever).

If you need further help please upload your file (maybe with anonymous data) on an online file hoster like www.dropbox.com and post the download link here.

A macro to anonymize data in selected cells can be downloaded here:

https://www.dropbox.com/s/rkfxuh85j5wyj9y/modAnonymize.bas?dl=1

Andreas.

This still fails on the same run time error "paste special method of range class failed" 2nd time around.

any ideas?

Please never use SELECT, SELECTION, ACTIVECELL, it is slow and error prone. Always refer to the objects directly.

A code that uses the RANGE directive only is easier to read for humans, see code below.

Andreas.

Private Sub CommandButton1_Click()

  Dim Lastrow As Long

  'Copy Data

  With Workbooks("Book2.xls").Sheets("sheet1")

    .Range("K4:K35,K38:K52").Copy

  End With

  With Workbooks("book1.xlsm")

    With .Sheets("sheet1")

      .Unprotect

      .Range("BA4").PasteSpecial Paste:=xlPasteValues

      'Cancel the copy immediately for compatibility to Excel 2016

      Application.CutCopyMode = False

    End With

    'Stock take

    With .Sheets("sheet2")

      .Unprotect

      .Range("AA8") = .Range("AB8").Value

      Lastrow = .Range("D" & .Rows.Count).End(xlUp).Row

      If Lastrow < 7 Then

        .Range("AA8").Copy .Range("D8")

      Else

        .Range("AA8").Copy .Range("D" & Lastrow + 1)

      End If

      Application.CutCopyMode = False

      .Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, _

        AllowFormattingColumns:=True, AllowFormattingRows:=True

    End With

  End With

End Sub

Norman, 

That has worked great however I have now added to the code and amended slightly of which it works on a single command. but when i try to run it again I am getting a run time error 1004 again for "paste special method of range class failed". 

If i end the debugger and run it again it works fine????

I have used the original code for the first step as copying from another workbook into sheet 1 then using your recommended for the next steps as copying within the same workbook into various other cells. 

Any input would be appreciated. 

see amended code below.

(The Command button is in book1, sheet1)

Private Sub CommandButton1_Click()

' Copy Data

    Windows("Book2.xls").Activate

With Sheets("sheet1")

    .Select

    .Range("K4:K35,K38:K52").Select

    Selection.Copy

    .Range("A1").Select

End With

    Windows("book1.xlsm").Activate

With Sheets("sheet1")

    .Select

    ActiveSheet.Unprotect

    .Range("BA4").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False (This is the run time error point 2nd time around)

    .Range("A1").Select

End With

'

'   Stock take

    With Sheets("sheet2")

    .Select

    ActiveSheet.Unprotect

    .Range("AA8") = .Range("AB8").Value

    Dim Lastrow As Long

Lastrow = ActiveSheet.Cells(Cells.Rows.Count, "D").End(xlUp).Row

    If Lastrow < 7 Then

        .Range("AA8").Copy ActiveSheet.Cells(8, 4)

    Else

        .Range("AA8").Copy ActiveSheet.Cells(Lastrow + 1, 4)

End If

    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _

        , AllowFormattingColumns:=True, AllowFormattingRows:=True

End With

End Sub