Paste Values Only

[jsturm]

I have a simple operation where I am trying to essentialling copy a column of values from sheet Processed Compilation Tab to another sheet ATS Get Delete List.
It works ok but after this operation is done I do some filtering in the source sheet and what i am finding is that using the method below, once some of the values are filtered out my new column in ATS Get Delete List values are changing to #REF .

How can I use this method to permanently just paste the values over to my column in ATS Get Delete List?

Dim iLastrow As Long
Dim xsheet As Worksheet
Dim wb As Workbook

Set wb = ThisWorkbook
Set xsheet = wb.Sheets("Processed Compilation Tab")
iLastrow = xsheet.Cells(xsheet.Rows.Count, "Q").End(xlUp).Row ' -4162 value of xlup

Application.ScreenUpdating = False

Sheets("ATS Get Delete List").Range("W2:W" & iLastrow).Value = "=Trim(Clean('Processed Compilation Tab'!Q2))"
iLastrow = Sheets("ATS Get Delete List").Cells(Sheets("ATS Get Delete List").Rows.Count, "Z").End(xlUp).Row

Application.ScreenUpdating = True

 

[Fluff]

Try

With Sheets("ATS Get Delete List").Range("W2:W" & iLastRow)
   .Value = "=Trim(Clean('Processed Compilation Tab'!Q2))"
   .Value = .Value
End With

 

[jsturm]

Awesome that worked great. I believe the only thing it changed then was my previous code had an effect to clear all cells W2:W in the process of pasting over the data.

Sometimes when I run this, there are less rows coming from the source sheet than the last time so I need to be sure all previous data is cleared out of the destination.
Would you recommend I just add a line for that to clear all contents from W:W & Last row in this case? When I tested this scenario as is now with only 250 rows being pasted in and the last time there was a 1000 rows, it left the data from the previous run.

If so, could you help me with the best way to state that 'clear contents' code?

Try

With Sheets("ATS Get Delete List").Range("W2:W" & iLastRow)
   .Value = "=Trim(Clean('Processed Compilation Tab'!Q2))"
   .Value = .Value
End With

 

[jsturm]

Would this work properly?

With Sheets("ATS Get Delete List").Range("W2:W" & iLastrow)
.Range("W2:W" & .Range("W2").End(xlDown).Row).ClearContents
.Value = "=Trim(Clean('Processed Compilation Tab'!Q2))"
.Value = .Value
End With

 

[Fluff]

Just add this

Range("W2:W" & Rows.Count).ClearContents

 

[jsturm]

Great, works perfectly!

 

[Fluff]

You're welcome