On Wednesday, March 19, 2014 11:37:20 PM UTC-7, Claus Busch wrote:
Hi Howard,



Am Wed, 19 Mar 2014 15:52:12 -0700 (PDT) schrieb L. Howard:



This is one of many attempts to get it to write to sheet2.

Is this a case like you describe above.



I would do it with the formula only once.

Try:



Sub A2_Down_Copy()

Dim lRowCount

Dim myArr As Variant



With Sheets("Sheet1")

lRowCount = .Cells(Rows.Count, "AE").End(xlUp).Row

With .Range("A2").Resize(lRowCount)

.Formula = "=CONCATENATE(AE2&AG2&AI2&AK2&AM2&AO2)"

.Value = .Value

myArr = Range("A2:A" & lRowCount)

End With

End With



Sheets("Sheet2").Range("B2").Resize(lRowCount) = myArr



End Sub





Regards

Claus B.

--

Thanks Claus. That works well for me. Sheet 1 copy is perfect.

The Sheet 2 copy was producing a ghost #N/A in row 2002. I did this and it went away.

Sheets("Sheet2").Range("B2").Resize(lRowCount - 1) = myArr

Row 2 and 2001 on both sheets are identical each time I test.

I did notice that Column AE2 is blank and the blank repeats every 20 rows. Does not affect the copy that your code does. All the blank rows are copied as 5 short phrases instead of 6. More troubleshooting to do.

But like your code, always top notch.

Thanks again.

Howard