VB for Excel:"paste"

I am using VB for Excel. I want to copy from a file and paste in another file.
I open the first file and write

Sheet.application.worksheets("Sheet1").range("A1:E1").select
Sheet.application.worksheets("Sheet1").range("A1:E1").copy

Then I open the second file (closing or not the first file does not make a difference) and write

Sheet.application.worksheets("Sheet1").range("A1:E1").select
Sheet.application.worksheets("Sheet1").range("A1:E1").paste

It gives me ERROR at the last line, it does not accept "paste". I tried all possible changes. Any help?

I don't recall exact syntax, however Excel has Selection object (it belongs to an Application object in the hierarchy if I am not mistaken) which in turn has the Paste method.
So, once you select your Range then use Selection.Paste instead of what you did.

Moved.

.Paste is a method of worksheet (and few other types of objects), it is not a method of Range so why you get the error.

To copy a range you don't need to select that range prior to copy.

Try this:

Code:

---

    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
           
    '-- assumed both workbooks already opened
    Set ws1 = Workbooks("BookName1.xls").Worksheets("Sheet1")
    '-- or if yourcode is in the Active workbook:
    ' Set ws1 = ThisWorkbook.Worksheets("Sheet1")
    Set ws2 = Workbooks("BookName2.xls").Worksheets("Sheet1")
   
    '-- only need specify the first cell of destination
    ws1.Range("A1:E1").Copy Destination:=ws2.Range("A1")

---

The last line above is the simplest way, however it can be replaced by:

Code:

---

    ws1.Range("A1:E1").Copy
    ws2.Paste Destination:=ws2.Range("A1")

---

or as below (not recommended):

Code:

---

    Workbooks("BookName2.xls").Activate
    ws2.Activate
    ws2.Range("A1").Select
    ws2.Paste

---

Un fortunatelly, it doesn't work!!

Unfortunatelly, as I put the code

Dim ws1 as worksheet

It gives " compilation error, it has not been defined the type defined by the user" ( I am translating the message from spanish)

Quote:

---

Originally Posted by Norah

It gives " compilation error, it has not been defined the type defined by the user" ( I am translating the message from spanish)

---

:confused: Where are you running this from?