1

I have the following code in which I am trying to copy data from one sheet to another in same workbook. When I run the code I get Runtime error -438

Sub Copy()

Sheets("Sheet1").Range("A1:D20").Copy

Sheets("Sheet2").Activate

Range("E1").Select

ActiveSheet.Paste

Application.CutCopyMode = False

End Sub
Mamoun Benghezal
  • 5,264
  • 7
  • 28
  • 33
taz
  • 95
  • 1
  • 1
  • 9

2 Answers2

2

Try the following code. You should not rely on Activate and Select.

Sub ZCopy()
    Sheets("Sheet1").Range("A1:D20").Copy
    Sheets("Sheet1").Paste Destination:=Worksheets("Sheet2").Range("E1")
    Application.CutCopyMode = False
End Sub

Interesting Reads

  1. MSDN
  2. How to avoid using Select in Excel VBA macros
Community
  • 1
  • 1
Pankaj Jaju
  • 5,371
  • 2
  • 25
  • 41
  • It gives me error saying "Member already exists in an object module from which this object module derives" – taz Apr 08 '15 at 14:32
  • It looks like you already have a sub with that name already present in your project – Pankaj Jaju Apr 08 '15 at 14:36
  • 3
    You can't declare a function, that shadows a member of a document-type module. That's why `Copy` caused an error when added in a worksheet module. Worksheet *already has* a `Copy` method. You'd need to choose a different name for the function, or declare the function in a standard module. – ThunderFrame Feb 02 '17 at 03:55
  • @ThunderFrame - True. Updated. – Pankaj Jaju Feb 02 '17 at 08:23
1

Do you have a particular need for copy and paste? This can be slow and inefficient. If you're just copying data from one sheet to another, you can set the values of one range equal to the values of another range and avoid the whole thing.

Sheets("Sheet2").Range("E1:H20").Value = Sheets("Sheet1").Range("A1:D20").Value

This will set the range from cells E1:H20 on Sheet2 to the same values as those in the range A1:D20 on Sheet1, which is effectively a copy and paste. I should add that this will work only for the values themselves.

If there is specific formatting (or formulas) that you need copied and pasted, this method won't work.

Soulfire
  • 4,218
  • 23
  • 33