Excel VBA 400 error

[jjl287]

Hi all,

I have a macro that is trying to copy rows over from one workbook to another based on whether the values in column AR (column 44) are not equal to 0. However, I'm getting an "error 400" every time i try to run it, without direction to which line is causing the error. Does anyone have any ideas as to what might be causing the problem? Below is my code:

Sub EIB_Populate()


Dim LastRow As Integer, i As Integer, erow As Integer


LastRow = ActiveSheet.Range(“B” & Rows.Count).End(xlUp).Row


For i = 6 To LastRow


If Cells(i, 44).Value <> 0 Then
ActiveSheet.Range(Cells(i, 1), Cells(i, 44)).Select
Selection.Copy


'Open EIB compiled workbook'


Dim y As Workbook


Set y = Workbooks.Open("C:\Users\37181\Documents\EIB Compiled.xlsx")


y.Sheets("Sheet1").Select




erow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
ActiveSheet.Cells(erow, 1).Select
ActiveSheet.PasteSpecial Paste:=xlPasteValues


End If


Next i


End Sub

Also don't know if it's relevant but I'm using Excel 2013. Thanks in advance for all your help!

 

[Joe4]

That message sometimes is returned in one of the worksheets is protected (and therefore cannot be updated).

If that is not the issue, use the F8 key to step through the code line-by-line and see where the error occurs.

 

[Norie]

Does this work?

Sub EIB_Populate()
Dim y As Workbook
Dim wsDst As Worksheet
Dim wsSrc As Worksheet
Dim LastRow As Long, i As Long, erow As Long

    Set wsSrc = ActiveSheet

    Set y = Workbooks.Open("C:\Users\37181\Documents\EIB Compiled.xlsx")

    Set wsDst = y.Sheets("Sheet1")

    With wsSrc
    
        LastRow = .Range("B" & Rows.Count).End(xlUp).Row

        For i = 6 To LastRow

            If .Cells(i, 44).Value <> 0 Then
                .Range(.Cells(i, 1), .Cells(i, 44)).Copy

                erow = wsDst.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
                wsDst.Cells(erow, 1).PasteSpecial Paste:=xlPasteValues

            End If

        Next i

    End With

End Sub

 

[jjl287]

Thanks for both of your replies. Joe, nothing on the sheet is protected so I don't think that's the problem. Also not sure what the problem is with F8 b/c that normally works but isn't working with this code, thus making it hard to identify where the problem lies..

Norie, that's really close to the final product that I'm looking for (kind of demoralizing how quickly you were able to do that...)! The only thing is that it's only copying the lowest row with a non-zero value, rather than all rows with a non-zero value (sorry if I didn't make that clear). For example, if I have 3 in AR6, 10 in AR7, and 5 in AR8, it'll only paste row 8 into "EIB Compiled". Do you know how to adjust this?

 

[Norie]

Do you have data in column A?

 

[jjl287]

No, data starts in column B.

 

[Norie]

If that's the case we need to use column B in the destination sheet to determine the row to copy with, if we use A then data will be copied but it will overwrite any previous data that was copied.

So try this.

Sub EIB_Populate()
Dim y As Workbook
Dim wsDst As Worksheet
Dim wsSrc As Worksheet
Dim LastRow As Long, i As Long, erow As Long

    Set wsSrc = ActiveSheet

    Set y = Workbooks.Open("C:\Users\37181\Documents\EIB Compiled.xlsx")

    Set wsDst = y.Sheets("Sheet1")

    With wsSrc
    
        LastRow = .Range("B" & Rows.Count).End(xlUp).Row

        For i = 6 To LastRow

            If .Cells(i, 44).Value <> 0 Then
                .Range(.Cells(i, 1), .Cells(i, 44)).Copy

                erow = wsDst.Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Row
                wsDst.Cells(erow, 1).PasteSpecial Paste:=xlPasteValues

            End If

        Next i

    End With

End Sub

 

[jjl287]

Ah, that makes sense, good catch. This is perfect! Thanks so much for your help Norie!