Forum Discussion
Help With Run-Time Error 9
Hi,
I was hoping that you guys might be able to help me with an issue I'm having. I can't seem to get my Macro to work correctly and can't for the life of me figure out why!
It should copy rows of data from Sheet 1 based on a specific value being met in column 1; then past the rows into Sheet 5.
Private Sub CommandButton1_Click()
a = Worksheets("Sheet1").Cells(Rows.Count, 1).End(x1Up).Row
For i = 2 To a
If Worksheets("Sheet1").Cells(i, 1).Value = "1001" Then
Worksheets("Sheet1").Rows(i).Copy
Worksheets("Sheet5").Activate
b = Worksheets("Sheet5").Cells(Rows.Count, 1).End(x1Up).Row
Worksheets("Sheet5").Cells(b + 1, 1).Select
ActiveSheet.Paste
Worksheets("Sheet1").Activate
End If
Next
Application.CutCopyMode = False
ThisWorkbook.Worksheets("Sheet1").Cells(1, 1).Select
End Sub
I seem to be getting a 'Run-Time Error 9' when ever I try and run the macro. Any ideas? I'm tearing my hair out!!
3 Replies
Replace the codes where you determine variable a and b like this:
a = Worksheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row and b = Worksheets("Sheet5").Range("A" & Rows.Count).End(xlUp).RowThen it should work.
EDIT: Looked at your original code again. Saw you have "x1Up" in the code that sets a and b. Change it to "xlUP" and it will work as well. So type the letter "L" in stead of number "1".
- Hi, thank you for your advice. I have corrected the code as advised, but I'm still getting the same error message. I'm stumped as to what is the cause, when I run the Debug it highlights the second line in yellow.
Your corrected code, my code or both?
Both codes worked for me in the test sheet I created myself. Perhaps something else causes the error.
