Copy and paste depending on cell criteria

[michud08]

Good evening all,

I have done a bit of VBA coding in the past but not had any formal training. I have had success from adapting other code I found and lots of trial and error!

I am trying to find a bit of code that will copy data from 2 cells but only if a criteria is met in another cell. So basically, when I select "closed" from column E, I want Serial No & Part No to be copied to the other sheet. My table will have rows added to it over time so needs to be dynamic. Hopefully the screenshots will help clarify.

Any help is really appreciated. I can't seem to figure this out at all.

Data sheet:-

dynamic copy of specific cells.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Serial No	Description	Part number	ordered	complete?
2	001	fairing	A1234	YES
3	002	access cover	B1234	NO			When closed is selected from the drop down in column E I want VBA code to copy "Serial No' & "Part No"of that line to "completed Labels" sheet in the respective boxes.
4	003	drain	C1234	YES
5	004	cover	D1234	YES
6
7
8
9
10
11
12
Data

Cells with Data Validation
Cell	Allow	Criteria
E2:E5	List	closed, open

Completed Labels Sheet:-

dynamic copy of specific cells.xlsm
	A	B	C	D	E	F
1
2	DELIVER TO LOGISTICS
3	Serial No					I
4	Part no
5
Completed Labels

 

[mumps]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your Data sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Make a selection in column E. The macro assumes that "completed Labels" sheet has the header in row 1 and the data starts in row 2. Also, if you have any merged cells, unmerge them. You should avoid using merged cells because they almost always cause problems for macros.

Private Sub Worksheet_Change(ByVal Target As Range) 
    If Target.CountLarge > 1 Then Exit Sub
    If Target.Column <> 5 Then Exit Sub
    Application.ScreenUpdating = False
    If Target = "closed" Then
        With Sheets("completed Labels")
            .Cells(2, Columns.Count).End(xlToLeft).Offset(0, 1) = Target.Offset(, -4)
            .Cells(3, Columns.Count).End(xlToLeft).Offset(0, 1) = Target.Offset(, -2)
        End With
    End If
    Application.ScreenUpdating = True
End Sub

 

[michud08]

mumps,

This is brilliant. Thank you so much, its saved me loads of time.

I have changed the lines that reference where to paste as it wasn't working quite right. Made a small change to the following code and works perfect (in green) I just commented your lines out as I may be able to use them later. The cells on the "completed labels" sheet are static and will always be B3 & B4.

Cheers again, you saved me a headache! Hope this might be of use to someone else.

If Target = "closed" Then
With Sheets("completed Labels")
.Range("B3") = Target.Offset(, -4)
.Range("B4") = Target.Offset(, -2)
' .Cells(2, Columns.Count).End(xlToLeft).Offset(0, 1) = Target.Offset(, -4)
' .Cells(3, Columns.Count).End(xlToLeft).Offset(0, 1) = Target.Offset(, -2)
End With

 

[mumps]

You are very welcome.