Subscript out of range error on this code...

[chazrab]

I keep getting "Subscript out of range" run time error code 9 on this code where indicated

Sub copyNonBlankData()
Dim erow As Long, lastrow As Long, i As Long
lastrow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To lastrow
If Sheet1.Cells(i, 1) <> “” Then
Sheets(“Sheet1”).Range(Cells(i, 1), Cells(i, 2)).Copy  - Subscript out of range error at this line 
Sheets(“sheet2”).Activate
erow = Sheet2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
ActiveSheet.Paste Destination:=Sheets(“sheet2”).Range(Cells(erow, 1), Cells(erow, 2))
Sheets(“sheet1”).Activate
End If
Next i
Application.CutCopyMode = False
End Sub

Can anyone see any reason why ?

Thanks for anyone's help

cr

 

[JoeMo]

Your quote marks (") look strange and test, as copied from my browser, to be CHAR 147 not 34 which is the usual quote mark. To test replace this bit on the errant line:

Sheets(“Sheet1”).Range(....

with this

Sheet1.Range(....

Do you still get a code 9 error on that line?

 

[chazrab]

Your quote marks (") look strange and test, as copied from my browser, to be CHAR 147 not 34 which is the usual quote mark. To test replace this bit on the errant line:

Sheets(“Sheet1”).Range(....

with this

Sheet1.Range(....

Do you still get a code 9 error on that line?

….this is becoming very interesting. Did exactly what you said, but now a new error message appears that says Run time error 424 "object required" when I run the code with this line

Sheet1.Range(Cells(i, 1), Cells(i, 5)).Copy

What is Excel telling us it doesn't like ?
PS the only other thing I can tell you is that I am running this code block from a button on a userform. But would that make any difference?

cr


Thx for your help
cr

 

[Rick Rothstein]

I keep getting "Subscript out of range" run time error code 9 on this code where indicated

Sub copyNonBlankData()
Dim erow As Long, lastrow As Long, i As Long
lastrow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To lastrow
If Sheet1.Cells(i, 1) <> “” Then
Sheets(“Sheet1”).Range(Sheets(“Sheet1”).Cells(i, 1), Sheets(“Sheet1”).Cells(i, 2)).Copy  - Subscript out of range error at this line 
Sheets(“sheet2”).Activate
erow = Sheet2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
ActiveSheet.Paste Destination:=Sheets(“sheet2”).Range(Cells(erow, 1), Cells(erow, 2))
Sheets(“sheet1”).Activate
End If
Next i
Application.CutCopyMode = False
End Sub

Can anyone see any reason why ?

Maybe you need to qualify the Cells call as shown in red above.

 

[chazrab]

Maybe you need to qualify the Cells call as shown in red above.

Hi Rick - sorry for getting back late - I had to test and retest this code to see if it would work. It doesn't. I changed my code
to reflect your changes in red. It got past that line fine. Please see explanation below. Sheet1 and Sheet2 were generic names. Below is the real
sheet names, and what I want to do along with the code that does not work:


REGISTER sheet has data entry values from A2:: K2 and data is currently on row 894 YTD. Column E or 5 has outstanding items not yet paid. They are NOT in continuous rows, so there are blank cells(rows) between items in this column.

I just want to copy ALL row values for which column E cell values are NOT BLANK from A to E of the REGISTER sheet to the REPORT sheet so I can generate an itemized report of all outstanding items and display the results in a userform (OUTITEMS) listbox. The REPORT sheet does not have to have a header row. The results can just be copied to cells A1:E1. Can this be done accurately using either a For Loop sequence or an Autofilter ?. The code below does not work correctly

Private Sub cmdCopyTest_Click()
Dim erow As Long, lastrow As Long, i As Long
lastrow = Sheets("REGISTER").Cells(Rows.Count, 5).End(xlUp).Row
For i = 2 To lastrow
If Sheets("REGISTER").Cells(i, 5) <> "" Then
Sheets("REGISTER").Range(Sheets("REGISTER").Cells(i, 1), Sheets("REGISTER").Cells(i, 5)).Copy 
Sheets("REPORT").Activate
erow = Sheets("REPORT").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
ActiveSheet.Paste Destination:=Sheets("REPORT").Range(Cells(erow, 1), Cells(erow, 5)) <----bugs out at this line for some reason
Sheets("REGISTER").Activate
End If
Next i
End Sub

Run time error 1004 'Application defined or object defined error' is generated at this line:
ActiveSheet.Paste Destination:=Sheets("REPORT").Range(Cells(erow, 1), Cells(erow, 5))


I am sorry for this lengthy explanation.

cr

 

[Norie]

Try this, but you might want to consider that when pasting you usually only need to specify the top left corner of the destination range, and you can specify the destination when using Copy.

With Sheets("REPORT")
    .Paste Destination:=.Range(.Cells(erow, 1), .Cells(erow, 5))
End With