Worksheet Code to auto cut/paste if a specific cell contains a value

[dtrick37]

Hello Everyone,
I am looking for some help on how to write a specific sheet code and I am having a difficult time finding the right coding.

What I am trying to accomplish is if a particular option is chosen from a drop down field that I have built, then the contents of cell B6 and C6 will be copied and pasted into cells B11 and C11 respectively.

Example
B6 = John
C6 = Smith
Drop down option "Paid" is selected (the trigger option) so
John is pasted in B11
Smith is pasted in C11

Any help would be greatly appreciated!

 

[Joe4]

Welcome to the Board!

What cell is this drop-down box found in?
Is it simply a Data Validation drop-down box?

 

[Joe4]

Assuming that it is a Data Validation box, and the drop-down is in cell B7, this VBA code will do what you want.

To put the code in the correct place to get it to run automatically, do the following:
- Right-click on the sheet tab name at the bottom of your sheet.
- Select "View Code"
- Copy and paste the following code in the resulting VB Editor code window.

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Address = Range("[COLOR=#ff0000]B7[/COLOR]").Address Then
        If Target = "Paid" Then
            Range("B6:C6").Copy Range("B11:C11")
'           Uncomment the following line if you want to clear B6:C6
            'Range("B6:C6").ClearContents
        End If
    End If
    
End Sub

If your drop-down is in a different cell location, change the part in red to the address of the cell where it is located.
If you want the cells B6:C6 cleared after the copy, remove the single quote at the beginning of this line:

            'Range("B6:C6").ClearContents

 

[dtrick37]

Joe4 Thank you for the quick reply
The drop down is a data validation. The drop down is located on D6 so I edited the code accordingly. When I enter it into the VBA screen however, the code does not seem to produce the result. The data validation is located on another hidden sheet so I am not sure if that would affect anything. Also, will the fact that I have another VBA code on the same coding sheet cause an issue? Thanks again!

 

[Joe4]

The drop down is located on D6

On the same sheet as the where the data currently resides and is being copied to?
In other words, is the data validation in cell D6 on the same sheet as the data in B6 and C6?
If so, it should work, if VBA is enabled and you followed the EXACT steps I told you where to put the code (it needs to be in the proper Sheet Module, it will not work in a General Module).

Also, will the fact that I have another VBA code on the same coding sheet cause an issue?

Is it another Worksheet_Change procedure in the SAME Sheet Module?
If so, please post the code. You can only have one Worksheet_Change event procedure per Sheet Module.

 

[dtrick37]

Joe4
The data validation is located on another sheet in the work book. I can move it to the same page if that would make the coding easier.

The other code is a Worksheet_Change event in which a time stamp generates once the name field is filled out. There are numerous "sections" that the time stamp is applied to hence all the targets. Code below:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
If Not Intersect(Target, Range("b6,b33,b60,b87,b113,b140")) Is Nothing Then
On Error Resume Next
Application.EnableEvents = False
Target.Offset(-3, 0) = Now
Application.EnableEvents = True
On Error GoTo 0
End If
End Sub

Thanks!

 

[Joe4]

So, the cell that they are filling out (D6) is on another sheet?
Note I am talking about the physical drop-down box, not where the values used to populate the drop-down exist.

The data validation is located on another hidden sheet so I am not sure if that would affect anything.

If the actual drop-down box is on a hidden sheet, how exactly are users filling it out?

If it is, indeed, on a different sheet, please let me know the name of that sheet as well as the name of the sheet where your data resides.

If the drop-down is actually on the same sheet as the data, you just need to combine the code I gave you with your other code so both code blocks are in that single Worksheet_Change procedure, like this:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
    
    If Not Intersect(Target, Range("b6,b33,b60,b87,b113,b140")) Is Nothing Then
        On Error Resume Next
        Application.EnableEvents = False
        Target.Offset(-3, 0) = Now
        Application.EnableEvents = True
        On Error GoTo 0
    End If
    
    If Target.Address = Range("D6").Address Then
        If Target = "Paid" Then
            Application.EnableEvents = False
            Range("B6:C6").Copy Range("B11:C11")
'           Uncomment the following line if you want to clear B6:C6
            'Range("B6:C6").ClearContents
            Application.EnableEvents = True
        End If
    End If
    
End Sub

 

[dtrick37]

Joe4
I am sorry for not explaining properly. The cells that the data validation pulls the information from is hidden, not the actual drop-down. I misunderstood you, if it isn't glaringly obvious I am a newb to all this! Thank you for your patience and continued help!

 

[dtrick37]

That worked perfectly! Thank you so much for your help!

 

[Joe4]

I figured that is maybe what you meant.
You are welcome!