Paste Special when moving data between worksheets

[VBAN0oB]

I am trying to figure out where to insert the paste special rule on my code. Currently each sheet has conditional formatting that isn't the same so the current code is overriding the conditional formatting on the new sheet it is pasted to.

'Apply loop for column A until last cell with value
For Each Cell In .Range("A1:A50" & .Cells(.Rows.Count, "D").End(xlUp).Row)
Selection.EntireRow.Select
'Apply condition to match the "Pipeline" value
If Cell.Value = "P" Then
Selection.EntireRow.Select
sheetNo2.Activate
tbl2.ListRows.Add
R = tbl2.Range.Rows.Count
sheetNo1.Activate
'Command to Copy and move to a destination Sheet "Pipeline"
.Rows(Cell.Row).Copy Destination:=sheetNo2.Rows(FinalRow2 + 1)
Selection.EntireRow.Select
.Rows(Cell.Row).Delete
FinalRow2 = FinalRow2 + 1

 

[rollis13]

Change this line:

.Rows(Cell.Row).Copy Destination:=sheetNo2.Rows(FinalRow2 + 1)

to:

.Rows(Cell.Row).Copy
sheetNo2.Rows(FinalRow2 + 1).PasteSpecial Paste:=xlPasteValues

 

[VBAN0oB]

Change this line:

.Rows(Cell.Row).Copy Destination:=sheetNo2.Rows(FinalRow2 + 1)

to:

.Rows(Cell.Row).Copy
sheetNo2.Rows(FinalRow2 + 1).PasteSpecial Paste:=xlPasteValues

Thank you so very much for the assist. You just saved me a ton of time in further research and in going behind people and deleting the conditional formatting that follows.

 

[rollis13]

Thanks for the positive feedback, glad having been of some help.