VBA Code stops working when run more than once

[Phanmore]

I am running a macro to move one line of data from my activesheet to another sheet.

THe first time I run my code it works fine, but on the second time I run the code I recieve the following error message:

Run Time Error '1004':

Paste method of worksheet class failed


This is my code

Private Sub CommandButton1_Click()
Userform1.Hide
ActiveCell.EntireRow.Copy
Sheets("Filled Jobs").Select
Range("A65536").End(xlUp).Offset(1, 0).Select
Sheets("Filled Jobs").Unprotect Password:="secret"
ActiveSheet.Paste
Sheets("Filled Jobs").Protect Password:="secret"
Sheets("Current Jobs").Select
ActiveSheet.Unprotect Password:="secret"
ActiveCell.EntireRow.Delete
ActiveSheet.Protect Password:="secret"
End Sub

Is there anything I can change to stop this from happening?

 

[Ruddles]

I think you're ending up with the wrong sheet active. Is that what seems to happens when you run it?

You could try putting Sheets("Current Jobs").Activate after the Userform1.Hide.

Otherwise I'm stumped (which doesn't take much).

 

[Phanmore]

Thanks for the response.

I added in the code, but still no joy. After looking into it a little further, the code generates an error becuase it is not completing the line of code to copy the data (2nd Line).

Any ideas why it would not be copying this line of data?

 

[Phanmore]

I found the problem, although I still don't know why it was a problem...

When the following line ran, it knocked out the copying eariler in the code.

Sheets("Filled Jobs").Unprotect Password:="secret"

I just moved this line prior to the copying and it fixed my problem, weird.

 

[snb_]

Private Sub CommandButton1_Click()
  Userform1.Hide
 
  Sheets("Current Jobs").Unprotect "secret"
 
  With Sheets("Filled Jobs")
    .Unprotect "secret"
    .cells(rows.count,1).End(xlUp).Offset(1 ).entirerow=ActiveCell.EntireRow.Value
    .Protect "secret"
  WEnd With
 
  ActiveCell.EntireRow.Delete
  Sheets("Current Jobs").protect "secret"
End Sub

Avoid, select, activate & copy in VBA