revise CONCATENATE code

[Howard]

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

 

[isabelle]

Le 2013-02-11 01:52, Howard a écrit :

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

without placing the formulas in range AT ?
if the cell range is very large, the current method is faster, but it
requires going through an intermediate column that then can be erase.
otherwise it is possible to go through a loop .
do you want to go through an intermediate column or go through a loop?

isabelle

 

[Howard]

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