locked
VBA - Loop Worksheets - Method Range of Object Failed RRS feed

  • Question

  • Hi friends,

    i am trying to make a copy and paste list between sheets. In a worksheet  have listed Source and destination and copy and paste locations , as per below.






    I think I am nearly there, but there is an error.

    Option Explicit
     
     
    Sub CopyBlocks()
    
    Dim i As Long
    Dim ws As Worksheet
    Dim oSrcWksht As Worksheet
    Dim oDestWksht As Worksheet
        
        
        Set ws = Worksheets("CopyList")
        
        
        For i = 2 To ws.Cells(ws.Rows.Count, "D").End(xlUp).Row      ' Worksheet Source
            
            Set oSrcWksht = Worksheets(ws.Cells(i, "D").Value)       ' Worksheet Source
            
            Set oDestWksht = Worksheets(ws.Cells(i, "F").Value)      ' Worksheet Destination
            
           oSrcWksht.Range(ws.Range(Cells(i, "E"))).Copy Destination:=oDestWksht.Range(ws.Cells(i, "G").Value)
        Next i
        
    End Sub
    

    I set it up logically and it makes sense to me so far, but I don't know what the problem is
    method range of object Failed.


    please advise


    Cheers Dan :)

    Wednesday, September 14, 2016 1:35 PM

Answers

  • ws.Range(Cells(i, "E")) is a bit much.

    Change the line

           oSrcWksht.Range(ws.Range(Cells(i, "E"))).Copy Destination:=oDestWksht.Range(ws.Cells(i, "G").Value)

    to

           oSrcWksht.Range(ws.Cells(i, "E").Value).Copy Destination:=oDestWksht.Range(ws.Cells(i, "G").Value)


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

    • Marked as answer by Dan_CS Wednesday, September 14, 2016 2:55 PM
    Wednesday, September 14, 2016 2:29 PM

All replies

  • ws.Range(Cells(i, "E")) is a bit much.

    Change the line

           oSrcWksht.Range(ws.Range(Cells(i, "E"))).Copy Destination:=oDestWksht.Range(ws.Cells(i, "G").Value)

    to

           oSrcWksht.Range(ws.Cells(i, "E").Value).Copy Destination:=oDestWksht.Range(ws.Cells(i, "G").Value)


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

    • Marked as answer by Dan_CS Wednesday, September 14, 2016 2:55 PM
    Wednesday, September 14, 2016 2:29 PM
  • Hi Hans,

    it worked!

    I only spent 3 hours googling all the wrong things.

    I thought it was a more serious problem and thought I may need a 3d loop, dont ask :)

    Thanks for spotting this error.

    Have a great day now :)

    Cheers Dan :)


    • Edited by Dan_CS Wednesday, September 14, 2016 2:55 PM
    Wednesday, September 14, 2016 2:55 PM