Transferring data from one sheet to another based on cell drop down at the end of any given row.

Rowland1986

New Member
Joined
May 15, 2018
Messages
8
I am currently looking over some code that was created by a staff member that has now left. The code is basic and works okay for what we need, however, there is one piece I would like to add in but cannot work out how do it.

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    Application.ScreenUpdating = False
    
''' IDENTIFIES IF A CELL IN COLUMN "Q" IS SHOWING AS "YES" '''
    If Target.Column = 17 Then
        If Target = "Yes" Then
            Dim Ans As Integer
            Dim NxtRow As Integer
            
''' CALCULATES NEXT BLANK ROW ON THE "ARCHIVE" WORKSHEET '''
            NxtRow = Sheets("Archive").Range("F" & Rows.Count).End(xlUp).Row + 1
            
''' CALLS A MESSAGE BOX ASKING FOR ARCHIVE CONFIRMATION '''
            Ans = MsgBox("Are You Sure You Have Completed This Case? Pressing YES Will Archive This Work!", vbOKCancel)
            
''' TRANSFERS ENTIRE ROW OF DATA FROM WORKSHEET TO ARCHIVE IF "OK" IS CLICKED IN THE MESSAGE BOX '''
            Select Case Ans
            Case vbOK
                Application.EnableEvents = False
                ThisWorkbook.Save
                Target.EntireRow.Copy _
                Destination:=Sheets("Archive").Range("A" & NxtRow)
                ThisWorkbook.Save
                Target.EntireRow.Delete
                Application.EnableEvents = True
                
''' REMOVES "YES" FROM CELL IN COLUMN "Q" AND CANCELS TRANSFER OF DATA TO THE "ARCHIVE" WORKSHEET IF "CANCEL" IS CLICKED IN THE MESSAGE BOX '''
            Case vbCancel
                Selection.ClearContents
                Exit Sub
            End Select
        End If
    End If
    
    Application.ScreenUpdating = True
End Sub

Currently what the code does is anytime someone chooses "yes" from the drop down at the end of any given row, a confirmation message box appears. By clicking "ok" the entire row transfers to a different sheet in the same workbook. Clicking cancel, removes the word "yes" and then does nothing.

What I am looking to add is, before running the code for the "ok" option, I would like it to check column "F" on the row that is currently selected and return a message box if that cell has been left blank and also cancel the transfer action until this cell in column "F" has been completed.

Any help with this issue would be greatly appreciated.
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Try adding this
Code:
''' CALCULATES NEXT BLANK ROW ON THE "ARCHIVE" WORKSHEET '''
            NxtRow = Sheets("Archive").Range("F" & Rows.Count).End(xlUp).row + 1
            [COLOR=#0000ff]If Range("F" & Target.row) = "" Then
               Target.Value = ""
               MsgBox "Column F is balnk"
               Exit Sub
            End If
[/COLOR]
 
Upvote 0
Glad to help & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,223,231
Messages
6,170,884
Members
452,364
Latest member
springate

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