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.
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