0

I have a very strange problem with excel vba. I have narrowed the problem down and made this code to represent the problem as simply as possible.

   Sub Button1_Click()

   Button1_Click Macro
       Range("A1:B17").Select
       Selection.Copy
       Workbooks.Add
       ActiveSheet.Paste
       Application.CutCopyMode = False
   End Sub

On most computers I have no issue, but in the company we have two new laptops that give me the following error message on ActiveSheet.Paste row.

Run time error '1004'
Paste method of Worksheet class failed

I have tested this on both Windows 10 and 7, and on different computers. Only ones causing problems are the two new ones with Win10.

I saw this thread, and also tried the last option there about adding delays within the code:

Run Time Error '1004': Paste Method Of worksheet Class Failed error

Any suggestions how to solve this, and/or what is causing this issue?

-Rasmus-

RazMan
  • 1
  • 1

1 Answers1

0

Do avoiding .Select.

And while pasting, do clearly determining the destination. Else it might be undefined because of the destination workbook was opened short time before.

Sub Button1_Click()

 ActiveSheet.Range("A1:B17").Copy
 Set newWB = Workbooks.Add
 newWB.Worksheets(1).Paste Destination:=newWB.Worksheets(1).Range("A1")
 Application.CutCopyMode = False

End Sub
Axel Richter
  • 56,077
  • 6
  • 60
  • 87
  • Hi, Thank you for the effort. No difference in the outcome. Still hoping for a solution/reason for this. – RazMan Jun 28 '18 at 11:21
  • @RazMan: Where is the workbook containing the macro stored? Is the `ActiveSheet` from which the range is copied a sheet in the workbook containing the macro? If not, how was it opened before? Sounds as if source sheet and target sheet while copy/paste are in different Excel application instances. Then of course pasting must fail. – Axel Richter Jun 28 '18 at 11:34