Run-Time Error 1004 with Copy/Paste VBA

[mkenny]

Hi

I'm trying to write a code that will copy data from a specific column in one workbook and paste that data in another workbook that has a matching column heading. In this case I'm copying data from one column with the heading "dog" to another column with the heading "dog." I will not always know which columns contain which heading, so the code will have to search through using a loop. I'm starting with searching through the first 5 columns.

"Run-Time Error '1004': Application-defined or object defined error" appears when I try to run the following code:

Sub ColMatch()



    Dim i As Integer
    
    For i = 1 To 5
    
        If Workbooks("Source Data WB - Copy data to WB").Sheets("Source").Cells(1, i) = "Dog" Then
        
            Workbooks("Source Data WB - Copy data to WB").Sheets("Source").Range(Cells(2, i), Cells(6, i)).Copy 'ERROR!!!!!
            
            
            'Selection.Copy
        
        End If
        
    Next i
        
    Dim j As Integer
        
    For j = 1 To 5
        
        If Workbooks("Target Data WB- Copy data to WB").Sheets("Target WB").Cells(1, j) = "Dog" Then
        
           Workbooks("Target Data WB- Copy data to WB").Sheets("Target WB").Range(Cells(2, i), Cells(6, i)).Select
           
           
           Workbooks("Target Data WB- Copy data to WB").Sheets("Target WB").Paste
           
           
        End If
        
    Next j
    
           
End Sub

Any help on this is greatly appreciated!

 

[ProfessorPorcupine]

Copy and paste is error prone, try storing the value in a variable instead. See below:

Sub ColMatch()

    Dim i As Integer
    Dim strValue As String
    
    For i = 1 To 5
        If Workbooks("Source Data WB - Copy data to WB").Sheets("Source").Cells(1, i) = "Dog" Then
            strValue = Workbooks("Source Data WB - Copy data to WB").Sheets("Source").Range(Cells(2, i), Cells(6, i))        
        End If
    Next i
        
    Dim j As Integer
        
    For j = 1 To 5
        If Workbooks("Target Data WB- Copy data to WB").Sheets("Target WB").Cells(1, j) = "Dog" Then
            Workbooks("Target Data WB- Copy data to WB").Sheets("Target WB").Range(Cells(2, i), Cells(6, i)) = strValue
        End If
    Next j
    
End Sub

 

[BiocideJ]

I can't completely test this, but I do notice a problem with your Cells() declarations within your Copy line.

you are fully defining the source of the .Range, but leaving Excel to its own devices in determining your .Cells parent. By default it is going to use the active worksheet as the parent (i.e. ActiveWorksheet.Cells(2,i) etc) which is almost certainly not what you are wanting here.
You will need to either preface all Cells() functions with their full paths:
e.g.
Workbooks("Source Data WB - Copy data to WB").Sheets("Source").Range(Workbooks("Source Data WB - Copy data to WB").Sheets("Source").Cells(2, i), Workbooks("Source Data WB - Copy data to WB").Sheets("Source").Cells(6, i)).Copy
or use a WITH statement to shorten it down and make it more legible.

Sub ColMatch()
 
    Dim i As Integer
    
    For i = 1 To 5
        With Workbooks("Source Data WB - Copy data to WB").Sheets("Source")
            If .Cells(1, i) = "Dog" Then
                
                .Activate
                .Range(.Cells(2, i), .Cells(6, i)).Copy
                
            
            End If
        End With
    Next i
        
    Dim j As Integer
        
    For j = 1 To 5
        With Workbooks("Target Data WB- Copy data to WB").Sheets("Target WB")
            If .Cells(1, j) = "Dog" Then
            
               .Range(.Cells(2, i), .Cells(6, i)).Select
               
               
               .Paste
               
               
            End If
        End With
    Next j
    
           
End Sub

I thought of one other potential pitfall whereas Excel needs the sheet you are copying from to be active when copying.
I added another line (in blue) that you can optionally try to use if it is not working without it.

 

[mkenny]

Thanks! I will give this a try.

 

[mkenny]

Your suggestions were very helpful. I ran into some errors when using the With statement, but in the end this is the code that worked

Sub ColMatch()



    Dim i As Integer
  
    
    
    For i = 1 To 5
    
        If Workbooks("Source Data WB - Copy data to WB").Sheets("Source").Cells(1, i) = "Dog" Then
        
                
                SorValue = Range(Workbooks("Source Data WB - Copy data to WB").Sheets("Source").Cells(2, i), Workbooks("Source Data WB - Copy data to WB").Sheets("Source").Cells(6, i))
            
        
        End If
    
    Next i
        
    Dim j As Integer
        
    For j = 1 To 5
        
        If Workbooks("Target Data WB- Copy data to WB").Sheets("Target WB").Cells(1, j) = "Dog" Then
        
                
                
                Range(Workbooks("Target Data WB- Copy data to WB").Sheets("Target WB").Cells(2, j), Workbooks("Target Data WB- Copy data to WB").Sheets("Target WB").Cells(6, j)) = SorValue
           
           
        End If
        
    Next j
    
           
End Sub

I'm very new to VBA so I'm not entirely sure why this works, but thank you very much for your help!

 

[BiocideJ]

I briefly described the reasoning before, but basically it boils down to this.
If you don't tell VBA where the Cell you are referencing is located, it will always *assume* it is the currently active sheet.
This applies to both the .Range and .Cell objects.
In your VBA statement, you were technically using 3 different Range/Cell objects, but only spelling out where 1 of them was located.

Once that line was executed, it was looking for the range of the "Source" sheet within the range of another sheet (whatever was active).
Since VBA couldn't make sense of that you got an error. The problem was fixed by just being overly specific so there was no ambiguity for the code to try to sort through.