Intermittent Run-time Error 9

[vbones]

I'm still new and learning VBA, so please be gentle. I have (attempted) to write a code so that a user can input information on a tab, hit submit, it will transfer the information to the 2nd tab and clear the info from the first tab. Also, the 2nd tab is locked so that the information cannot be altered (except by the person with the password). The spreadsheet was created in Excel 2003. This works fine on my computer, but some other people are getting the "Run-time Error '9': Subscript out of range" error when they try to hit the submit button, but it doesn't always happen. They are using Excel 2007. I don't understand why this is happening. Also, I'm sure my attempt at code could probably be cleaned up a little. Any suggestions?

Thanks!

Here is the code being used: (and if you have suggestions to make it better, I would love to learn!)

Private Sub CommandButton1_Click()
Worksheets("sheet2").Unprotect Password:="help"
nextrow = Worksheets("sheet2").Range("A65536").End(xlUp).Row + 1
Worksheets("sheet2").Cells(nextrow, 1).Resize(1, 16).Value = Array(Worksheets("log").Range("a2"), Worksheets("log").Range("b2").Value, Worksheets("log").Range("c2").Value, Worksheets("log").Range("d2").Value, Worksheets("log").Range("e2").Value, Worksheets("log").Range("f2").Value, Worksheets("log").Range("g2").Value, Worksheets("log").Range("h2").Value, Worksheets("log").Range("i2").Value, Worksheets("log").Range("j2").Value, Worksheets("log").Range("k2").Value, Worksheets("log").Range("l2").Value, Worksheets("log").Range("m2").Value, Worksheets("log").Range("n2").Value, Worksheets("log").Range("o2").Value, Worksheets("log").Range("p2").Value)
MsgBox "Has been submitted"

Worksheets("log").Range("a22").ClearContents
Worksheets("sheet2").Protect Password:="help"
End Sub

 

[VoG]

When the error occurs, click Debug. Which line of code is highlighted?

 

[pgc01]

Hi vbones
Welcome to the board

A simpler version of your code:

Private Sub CommandButton1_Click()

Worksheets("sheet2").Unprotect Password:="help"
nextrow = Worksheets("sheet2").Columns("A").End(xlUp).Row + 1
 
Worksheets("sheet2").Cells(nextrow, 1).Resize(1, 16).Value = _
    Worksheets("log").Range("a2:p2").Value
 
MsgBox "Has been submitted"
Worksheets("log").Range("a2:p2").ClearContents
Worksheets("sheet2").Protect Password:="help"
End Sub

 

[vbones]

Unfortunately, I haven't been able to get anyone to click the debug to highlight the error. When I got one of the users on the phone to do that, it worked for her with no problem! I can't do it because no matter what I do, it always seems to work for me. I'm going to try again tomorrow.

 

[vbones]

Thank you for the simplier version of the code! I knew there had to be a way, I just don't know enough yet to be able figure some of it out.

 

[vbones]

I figured out what the problem was. One of the users had changed the name of the tabs without telling me which, of course, threw everything off! Now that the tabs are named the same as what the code says they are named, everything seems to be working just fine! Thank you!