How to copy and paste data with values only when a condition is met?

[kev_bez]

Good afternoon all, I have this code below which is copying and pasting data from sheet3 to sheet 47 when column F has either "RI" or "Issue" written in it. The code works but it is also pasting any formulas and comments I have in the data. Is there a way to make this work with values only?

Thank you!

Sub Live_CWO()
Dim StatusCol As Range
Dim Status As Range
Dim Pastecell As Range

Set StatusCol = Sheet3.Range("F4:F50")
For Each Status In StatusCol

If Sheet47.Range("A2") = "" Then
Set Pastecell = Sheet47.Range("A2")
Else
Set Pastecell = Sheet47.Range("A1").End(xlDown).Offset(1, 0)

End If

If Status = "RI" Then Status.Offset(0, -2).Resize(1, 5).Copy Pastecell
If Status = "ISSUE" Then Status.Offset(0, -2).Resize(1, 5).Copy Pastecell


Next Status

End Sub

 

[Joe4]

Typically, you would avoid that by setting the cell values directly equal to one another instead of using Copy/Paste, i.e.
Range(destination cell).Value = Range(source cell).Value

If you need help modifying your code, please an example of your source data and expected result, so we can see what everything looks like.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[kev_bez]

Good morning, apologies for the extremely late reply. I managed to install XL2BB. What part of the code would I replace?
I want the code to look in column F of Sheet 3 if any of the cells have the text "ISSUE" OR "RI" and if condition is true then to copy columns D,E,F,G,H of that paticular row and paste onto a seperate sheet called Live CWO (Sheet 47) but I need as values only.
The code works at the moment but it copies the formulas from columns G & H rather than just the values.

Thank you!

UNDERBODY SQUAD Wk40.xlsm
	A			D	E	F	G	H	I	J	K	L	M	N	O	P
1	PRINT WO (ENTER "Y")			z8.1 - ALL SAFETY AREAS
2
3	8.1			Fault	CCAR #	Owner	Occ	DT	Wed PM	Wed N	Thu AM	Thu PM	Thu N	Fri AM	Fri PM	Fri N
4				02: * Rob_In[4,0].4 100R01 do5 DCP FAULT	224	RI	3	2.25	1.65	3.02	0.00	21.32	9.04	0.00	0.00	9.02
5				04: . _005S502 TROLLEY STOP	205	RI	2	87.84	4.13	8.84	7.73	0.00	2.86	3.95	0.00	19.49
6				10: . _071S513 LH Front Baffle Check	CWO_83 iROB CCAR	ESCALATE	2	1.39	1.38	20.39	16.33	19.08	14.77	6.20	7.83	10.38
7				19: . _300S222 ST300 PALLET IN POSITION 2	213	RI	5	26.28	0.94	10.28	2.84	3.64	5.20	5.84	1.80	10.08
8				15: * Rob_In[11,0].4 040R04 do5 DCP FAULT	CWO_94	RI	3	9.97	2.86	21.62	9.69	7.75	19.79	0.00	0.00	0.00
9				12: . _EZ81_034.SA07_OK SA07 Safety Area Reset Required	CWO_2 CCAR 33	ESCALATE	5	21.95	22.30	3.94	2.41	0.00	12.77	9.09	5.23	0.59
10				09: * Rob_In[18,0].4 250R09 do5 DCP FAULT	208	CLOSED	0	0.00	0.00	0.00	16.39	20.19	46.70	0.00	0.00	0.00
11				06: * Rob_In[12,0].4 230R05 do5 DCP FAULT	210	CLOSED	0	0.00	9.78	0.00	0.00	7.55	35.56	41.33	0.00	0.00
12				01: . Rob_In[2,0].11 210R02 do12 ON PATH			0	0.00	3.47	0.37	1.66	0.74	7.70	1.17	0.00	3.36
13				13: . Rob_In[12,0].0 080R04 do1 DRIVES ON	209	RI	0	0.00	3.21	4.72	12.34	2.72	4.36	0.00	2.02	7.55
14				06: * Rob_In[10,0].4 230R01 do5 DCP FAULT	CCAR 17	D.Greenwell S.Ansell	0	0.00	0.00	1.79	6.06	0.00	5.83	1.34	2.94	2.84
15				19: * Rob_In[16,0].4 300R06 do5 DCP FAULT	135	MON	9	24.72	0.00	0.24	0.00	0.00	0.00	7.10	0.00	3.78
16				13: . _SA07_Safety_Area_OK _SA07 Safety Area / Maint Zone + Gates OK	CWO_3 CCAR 33	ESCALATE	0	0.00	22.30	3.94	0.82	0.00	11.13	0.00	0.00	0.59
17				07: * Rob_In[16,0].17 240R05 do18 AREA RELEASE 2 RIVET CAR	238	ISSUE	0	0.00	1.64	8.33	2.02	2.28	1.07	1.41	0.96	0.88
18				02: . _EZ65_035.Estops_OK EZ65_035 PLC ESTOP OK			3	36.01	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
19				07: * Rob_In[15,0].4 240R02 do5 DCP FAULT	225	MON	1	3.84	0.83	4.81	3.68	1.69	0.00	2.01	19.31	0.00
20				13: * Rob_In[12,0].4 080R04 do5 DCP FAULT	239	ISSUE	2	2.57	0.82	10.79	4.10	4.07	0.58	0.00	7.92	7.66
21				01: * Rob_In[1,0].4 090R01 do5 DCP FAULT	157	MON	0	0.00	0.00	0.00	0.00	10.70	0.00	5.23	5.28	10.16
22				02: * Rob_In[5,0].4 100R02 do5 DCP FAULT	173	MON	1	1.12	0.78	11.18	2.46	0.00	2.78	0.00	6.61	16.36
23				05: * Rob_In[15,0].4 120R05 do5 DCP FAULT	187	CLOSED	0	0.00	24.31	26.15	2.53	0.00	0.00	0.00	0.00	0.00
24				08: * Robot2.ZSF3_Err 430R01 SAFEMOVE ZONE 3 VIOLATED			1	3.32	0.66	5.87	0.00	9.26	0.27	3.91	0.00	7.61
25				08: * Rob_In[6,0].4 070R03 do5 DCP FAULT			1	1.42	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
26				05: * Rob_In[14,0].4 120R02 do5 DCP FAULT			0	0.00	0.00	0.00	0.00	1.79	0.00	0.00	0.00	0.00
z8.1

Cell Formulas
Range	Formula
G4:G26	G4	=IFERROR(XLOOKUP(D4,'Ref 8.1'!E:E,'Ref 8.1'!B:B,0),0)
H4:H26	H4	=IFERROR(XLOOKUP(D4,'Ref 8.1'!E:E,'Ref 8.1'!C:C,0),0)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D997:D1048576,D1:D833	Cell Value	duplicates	text	NO
D1:D814,D997:D1048576,D823:D833	Cell Value	duplicates	text	NO
D1:D808,D997:D1048576,D823:D833	Cell Value	duplicates	text	NO
D1:D794,D997:D1048576,D823:D833	Cell Value	duplicates	text	NO
D1:D786,D997:D1048576,D823:D833	Cell Value	duplicates	text	NO
D1:D784,D997:D1048576,D823:D833	Cell Value	duplicates	text	NO
D1:D769,D997:D1048576,D823:D833	Cell Value	duplicates	text	NO
D1:D767,D997:D1048576,D823:D833	Cell Value	duplicates	text	NO
D1:D764,D997:D1048576,D823:D833	Cell Value	duplicates	text	NO
D1:D762,D997:D1048576,D823:D833	Cell Value	duplicates	text	NO
D1:D757,D997:D1048576,D823:D833	Cell Value	duplicates	text	NO
D1:D751,D997:D1048576,D823:D833	Cell Value	duplicates	text	NO
D1:D737,D997:D1048576,D823:D833	Cell Value	duplicates	text	NO
D1:D732,D997:D1048576,D823:D833	Cell Value	duplicates	text	NO
D1:D731,D997:D1048576,D823:D833	Cell Value	duplicates	text	NO
D1:D723,D997:D1048576,D823:D833	Cell Value	duplicates	text	NO
D1:D722,D997:D1048576,D823:D833	Cell Value	duplicates	text	NO
D1:D716,D997:D1048576,D823:D833	Cell Value	duplicates	text	NO
D1:D713,D997:D1048576,D823:D833	Cell Value	duplicates	text	NO
D1:D711,D997:D1048576,D823:D833	Cell Value	duplicates	text	NO
D1:D708,D997:D1048576,D823:D833	Cell Value	duplicates	text	NO
D1:D700,D997:D1048576,D823:D833	Cell Value	duplicates	text	NO
D1:D699,D997:D1048576,D823:D833	Cell Value	duplicates	text	NO
D1:D692,D997:D1048576,D823:D833	Cell Value	duplicates	text	NO
D1:D691,D997:D1048576,D823:D833	Cell Value	duplicates	text	NO
D1:D681,D997:D1048576,D823:D833	Cell Value	duplicates	text	NO
D1:D679,D997:D1048576,D823:D833	Cell Value	duplicates	text	NO
D1:D671,D997:D1048576,D823:D833	Cell Value	duplicates	text	NO
D1:D668,D997:D1048576,D823:D833	Cell Value	duplicates	text	NO
D1:D666,D997:D1048576,D823:D833	Cell Value	duplicates	text	NO
D1:D659,D997:D1048576,D823:D833	Cell Value	duplicates	text	NO
D1:D651,D997:D1048576,D823:D833	Cell Value	duplicates	text	NO
D1:D637,D997:D1048576,D823:D833	Cell Value	duplicates	text	NO
D1:D633,D997:D1048576,D823:D833	Cell Value	duplicates	text	NO
D1:D624,D997:D1048576,D823:D833	Cell Value	duplicates	text	NO
D1:D617,D997:D1048576,D823:D833	Cell Value	duplicates	text	NO
G1:H2	Cell Value	duplicates	text	NO
D1	Cell Value	duplicates	text	NO
D1,D3:D348,D997:D1048576,D823:D833	Cell Value	duplicates	text	NO
D1,D3:D340,D997:D1048576,D823:D833	Cell Value	duplicates	text	NO
D1,D3,D997:D1048576,D823:D833	Cell Value	duplicates	text	NO
D1,D3:D60,D997:D1048576,D823:D833	Cell Value	duplicates	text	NO
D1,D3:D89,D997:D1048576,D823:D833	Cell Value	duplicates	text	NO
D1,D3:D127,D997:D1048576,D823:D833	Cell Value	duplicates	text	NO
D1,D3:D187,D997:D1048576,D823:D833	Cell Value	duplicates	text	NO
D1,D3:D199,D997:D1048576,D823:D833	Cell Value	duplicates	text	NO
D1,D3:D213,D997:D1048576,D823:D833	Cell Value	duplicates	text	NO
D1,D3:D223,D997:D1048576,D823:D833	Cell Value	duplicates	text	NO
D1,D3:D234,D997:D1048576,D823:D833	Cell Value	duplicates	text	NO
D1,D3:D245,D997:D1048576,D823:D833	Cell Value	duplicates	text	NO
D1,D3:D251,D997:D1048576,D823:D833	Cell Value	duplicates	text	NO
D1,D3:D263,D997:D1048576,D823:D833	Cell Value	duplicates	text	NO
D1,D3:D269,D997:D1048576,D823:D833	Cell Value	duplicates	text	NO
D1,D3:D279,D997:D1048576,D823:D833	Cell Value	duplicates	text	NO
D1,D3:D296,D997:D1048576,D823:D833	Cell Value	duplicates	text	NO
D1,D3:D302,D997:D1048576,D823:D833	Cell Value	duplicates	text	NO
D1,D3:D307,D997:D1048576,D823:D833	Cell Value	duplicates	text	NO
D1,D3:D312,D997:D1048576,D823:D833	Cell Value	duplicates	text	NO
D1,D3:D319,D997:D1048576,D823:D833	Cell Value	duplicates	text	NO
D1,D3:D331,D997:D1048576,D823:D833	Cell Value	duplicates	text	NO
D3:E3,D997:E1048576,D823:E833,E4,E5:F5,E6:E800,E834:E996,E802:E822	Cell Value	duplicates	text	NO
D1,D3:D360,D997:D1048576,D823:D833	Cell Value	duplicates	text	NO
D1,D3:D368,D997:D1048576,D823:D833	Cell Value	duplicates	text	NO
D1,D3:D381,D997:D1048576,D823:D833	Cell Value	duplicates	text	NO
D1,D3:D390,D997:D1048576,D823:D833	Cell Value	duplicates	text	NO
D1,D3:D398,D997:D1048576,D823:D833	Cell Value	duplicates	text	NO
D1,D3:D415,D997:D1048576,D823:D833	Cell Value	duplicates	text	NO
D1,D3:D425,D997:D1048576,D823:D833	Cell Value	duplicates	text	NO
D1:D428,D997:D1048576,D823:D833	Cell Value	duplicates	text	NO
D1:D434,D997:D1048576,D823:D833	Cell Value	duplicates	text	NO
D1:D440,D997:D1048576,D823:D833	Cell Value	duplicates	text	NO
D1:D462,D997:D1048576,D823:D833	Cell Value	duplicates	text	NO
D1:D467,D997:D1048576,D823:D833	Cell Value	duplicates	text	NO
D1:D476,D997:D1048576,D823:D833	Cell Value	duplicates	text	NO
D1:D488,D997:D1048576,D823:D833	Cell Value	duplicates	text	NO
D1:D495,D997:D1048576,D823:D833	Cell Value	duplicates	text	NO
D1:D498,D997:D1048576,D823:D833	Cell Value	duplicates	text	NO
D1:D503,D997:D1048576,D823:D833	Cell Value	duplicates	text	NO
D1:D508,D997:D1048576,D823:D833	Cell Value	duplicates	text	NO
D1:D511,D997:D1048576,D823:D833	Cell Value	duplicates	text	NO
D1:D515,D997:D1048576,D823:D833	Cell Value	duplicates	text	NO
D1:D518,D997:D1048576,D823:D833	Cell Value	duplicates	text	NO
D1:D527,D997:D1048576,D823:D833	Cell Value	duplicates	text	NO
D1:D592,D997:D1048576,D823:D833	Cell Value	duplicates	text	NO
D1:D592,D997:D1048576,D823:D833	Cell Value	duplicates	text	NO
D1:D604,D997:D1048576,D823:D833	Cell Value	duplicates	text	NO
D1:D607,D997:D1048576,D823:D833	Cell Value	duplicates	text	NO
D1:D612,D997:D1048576,D823:D833	Cell Value	duplicates	text	NO
D4:D36	Cell Value	duplicates	text	YES

 

[Joe4]

copy columns D,E,F,G,H of that paticular row and paste onto a seperate sheet called Live CWO (Sheet 47) but I need as values only.

Can you post what your destination sheet (Live CWO, Sheet 47) looks like, so we can see the structure of that?

 

[kev_bez]

Currently it is like this but It is open to be modified to suit.

Can you post what your destination sheet (Live CWO, Sheet 47) looks like, so we can see the structure of that?

UNDERBODY SQUAD Wk40.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1
2	UB
3	No	Zone	DT/Occ	Owner	Returned		No	Zone	DT/Occ	Owner	Returned
4
5
6
7
8
9
10
11
12
13
14
15
16
17	FRM						FRM
18	No	Zone	DT/Occ	Owner	Returned		No	Zone	DT/Occ	Owner	Returned
19
20
21
22
23
24
25
26
27
28
29
30
31
32	CLO						CLO
33	No	Zone	DT/Occ	Owner	Returned		No	Zone	DT/Occ	Owner	Returned
34
35
36
37
38
39
40
41
42
Sh

 

[Joe4]

Try changing this part of your code:

If Status = "RI" Then Status.Offset(0, -2).Resize(1, 5).Copy Pastecell
If Status = "ISSUE" Then Status.Offset(0, -2).Resize(1, 5).Copy Pastecell

to this:

If Status = "RI" Then
    Status.Offset(0, -2).Resize(1, 5).Copy
    Pastecell.PasteSpecial Paste:=xlPasteValues
End If

If Status = "ISSUE" Then
    Status.Offset(0, -2).Resize(1, 5).Copy
    Pastecell.PasteSpecial Paste:=xlPasteValues
End If

 

[kev_bez]

Try changing this part of your code:

If Status = "RI" Then Status.Offset(0, -2).Resize(1, 5).Copy Pastecell
If Status = "ISSUE" Then Status.Offset(0, -2).Resize(1, 5).Copy Pastecell

to this:

If Status = "RI" Then
    Status.Offset(0, -2).Resize(1, 5).Copy
    Pastecell.PasteSpecial Paste:=xlPasteValues
End If

If Status = "ISSUE" Then
    Status.Offset(0, -2).Resize(1, 5).Copy
    Pastecell.PasteSpecial Paste:=xlPasteValues
End If

That has worked perfectly! Thank you ver much! Appreciate you taking time to help me out!

 

[Joe4]

You are welcome.
Glad I was able to help!