locked
VBA Excel - Copy Paste Array RRS feed

  • 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 :)

    Tuesday, November 7, 2017 5:19 PM

Answers

  • 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)

    • Marked as answer by Dan_CS Tuesday, November 7, 2017 10:25 PM
    Tuesday, November 7, 2017 5:50 PM
  • Sorry about that, it should have been

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


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

    • Marked as answer by Dan_CS Tuesday, November 7, 2017 10:25 PM
    Tuesday, November 7, 2017 9:42 PM

All replies

  • 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)

    • Marked as answer by Dan_CS Tuesday, November 7, 2017 10:25 PM
    Tuesday, November 7, 2017 5:50 PM
  • 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 :)

    Tuesday, November 7, 2017 6:41 PM
  • Sorry about that, it should have been

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


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

    • Marked as answer by Dan_CS Tuesday, November 7, 2017 10:25 PM
    Tuesday, November 7, 2017 9:42 PM
  • 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 :)


    • Edited by Dan_CS Tuesday, November 7, 2017 10:26 PM
    Tuesday, November 7, 2017 10:22 PM
  • 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 :)

    Tuesday, November 7, 2017 10:25 PM
  • 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)

    Tuesday, November 7, 2017 10:28 PM