On Sunday, February 10, 2013 10:52:48 PM UTC-8, Howard wrote:
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

I would be willing to give the LOOP a shot.

From this sheet:
Set Ws = Sheets("Title Generator")

To this sheet Range("K5") and down:
Set WSsuc = Sheets("Search Upr Case-Replace Sec #1")

The cell range could be around 1000+ rows... and what you supplied does a very decent job. Perhaps just leave it alone?

I will heed your advice.

Thanks, Isabelle