Isabelle helped me get my code just as I asked. It concatenate's a number of columns of data and transfers it to a range on the same sheet as the data and then converts the formulas to values. I then added a line to copy that data to another worksheet (in the same workbook).

Is there a way to go straight to the final worksheet with values?

Sheets("Search Upr Case-Replace Sec #1") Range("K5"):and down is the final destination.



Sub ConcTitle()
'BY_Isb ctrl + shift + H
Dim LastRow As Long
Dim Ws As Worksheet
Dim WSsuc As Worksheet
Set Ws = Sheets("Title Generator")
Set WSsuc = Sheets("Search Upr Case-Replace Sec #1")

LastRow = Ws.Range("C" & Ws.Rows.Count).End(xlUp).Row
Ws.Range("AT8:AT" & LastRow).Formula = _
"=C8&D8&E8&F8&G8&H8&I8&J8&K8&L8&M8&N8&O8&P8"
Ws.Range("AT8:AT" & LastRow) = Ws.Range("AT8:AT" & LastRow).Value

'now copy to Sheets("Search Upr Case-Replace Sec #1") K5

WSsuc.Range("K5:K" & LastRow) = Ws.Range("AT8:AT" & LastRow).Value

End Sub

Thanks.
Howard