Paste Method of Worksheet class failed error

Luminar

New Member
Joined
Oct 31, 2022
Messages
1
Office Version
  1. 365
Platform
  1. Windows
The goal of this spreadsheet is the change value so it moves to the appropriate sheet. I would also like to add a code into the first sheet where it will move an entry to the completed sheet based off the value(forms due) on top of the existing code of moving an item to the examiner sheet. I've been having difficulty with that as well.

The problem I am running into is the Run-Time error 1004, failing to paste into the Sheet 2(examiner sheet). It will work after I click end on the debug and click another entry. I don't seem to have any problems with entries being moved from Sheet 2(examiner) to Sheet 4(completed).

I am trying to keep the code as simple as possible as many individuals will be accessing this document in real time and working off of it.



VBA Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

unending

Application.Wait (Now + TimeValue("0:00:01"))

A = Worksheets("Interviewer").Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To A
If Worksheets("Interviewer").Cells(i, 5).Value = "Forms Due" Then
DoEvents
Worksheets("Interviewer").Rows(i).Cut
Worksheets("Examiner").Activate
B = Worksheets("Examiner").Cells(Rows.Count, 1).End(xlUp).Row
Worksheets("Examiner").Cells(B + 1, 1).Select
ActiveSheet.Paste
Worksheets("Interviewer").Activate
DoEvents
End If
Next

For i = 2 To A
If Worksheets("Interviewer").Cells(i, 1).Value = "" Then
Rows(i).Delete
End If
Next

ending

End Sub


Sub unending()

ThisWorkbook.Sheets("Interviewer").unprotect "test123"
ThisWorkbook.Sheets("Examiner").unprotect "test123"



End Sub

Sub ending()

ThisWorkbook.Sheets("Interviewer").protect "test123"
ThisWorkbook.Sheets("Examiner").protect "test123"

End Sub

VBA Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

unending

A = Worksheets("Examiner").Cells(Rows.Count, 1).End(xlUp).Row

For i = 2 To A
If Worksheets("Examiner").Cells(i, 8).Value = "Completed" Then
Worksheets("Examiner").Rows(i).Cut
Worksheets("Completed").Activate
B = Worksheets("Completed").Cells(Rows.Count, 1).End(xlUp).Row
Worksheets("Completed").Cells(B + 1, 1).Select
ActiveSheet.Paste
Worksheets("Examiner").Activate
End If
Next

For i = 2 To A
If Worksheets("Examiner").Cells(i, 1).Value = "" Then
Rows(i).Delete
End If
Next

ending

End Sub

Sub unending()

ThisWorkbook.Sheets("Examiner").unprotect "test123"
ThisWorkbook.Sheets("Completed").unprotect "test123"



End Sub

Sub ending()

ThisWorkbook.Sheets("Examiner").protect "test123"
ThisWorkbook.Sheets("Completed").protect "test123"

End Sub
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Forum statistics

Threads
1,223,956
Messages
6,175,614
Members
452,661
Latest member
Nonhle

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top