VBA Excel - Copy Paste Array

Question

Hi friends,

 

I am trying to write a copy paste array.

 

I get an error object worksheet not found

 

 

Here is my code

 

 Sub Copy_Paste_Array()
    
   
    Dim i           As Long
    Dim ows         As Excel.Worksheet
    Dim oSWksht     As Excel.Worksheet
    Dim oDWksht     As Excel.Worksheet


   oSWksht = ActiveWorkbook.Worksheets("AA")
   
   oDWksht = ActiveWorkbook.Worksheets("BB")

   oCopyRange = Array("A1", "A2")
    
   oDestinationRange = Array("A1", "A2")
    
    
    For i = LBound(oCopyRange) To UBound(oCopyRange)
    
    oSWksht.Range(oCopyRange(i)).Copy Destination:=oDWksht.Range(oDestinationRange(i).Value)
    
    Next i


  End Sub

 

 

Please may some one look at it

 

Thank you for your time

---

Cheers Dan :)

Answer 1

Since worksheets are objects, you have to use the keyword Set to assign a value to a variable of type Worksheet:

   Set oSWksht = ActiveWorkbook.Worksheets("AA")
  
   Set oDWksht = ActiveWorkbook.Worksheets("BB")

---

Regards, Hans Vogelaar (http://www.eileenslounge.com)

Answer 2

Hello Hans,

I fixed that thank you.

I still get object required on

oSWksht.Range(oCopyRange(i)).Copy Destination:=oDWksht.Range(oDestinationRange(i).Value)

:(

---

Cheers Dan :)

Answer 3

Sorry about that, it should have been

        oSWksht.Range(oCopyRange(i)).Copy Destination:=oDWksht.Range(oDestinationRange(i))

---

Regards, Hans Vogelaar (http://www.eileenslounge.com)

Answer 4

Hello Hans,

thank you for your kind help.

Sub Copy_Paste_Array()
    

    Dim oSWksht     As Worksheet
    Dim oDWksht     As Worksheet
    Dim i           As Long
    

    Set oSWksht = ActiveWorkbook.Worksheets("AA")
   
    Set oDWksht = ActiveWorkbook.Worksheets("BB")
    
    

    oCopyRange = Array("A1", "A2")
    
    oDestinationRange = Array("A30", "A20")
    '----------------------------------------
    
    
    
    For i = LBound(oCopyRange) To UBound(oCopyRange)

    oSWksht.Range(oCopyRange(i)).Copy Destination:=oDWksht.Range(oDestinationRange(i))
    
    Next i


  End Sub
         

Here is the fixed code working nicely :)

---

Cheers Dan :)

Answer 5

Hello Hans, 

 

It copied it nicely , sorry my second array term was empty  doh!

 

Its been a long day

 

Thank you as always

 

---

Cheers Dan :)

Answer 6

The code operates as intended when I run it. Make sure that A1 and A2 on sheet AA contain a value...

---

Regards, Hans Vogelaar (http://www.eileenslounge.com)