Thanks Incidental. That helps a bit, but I have some other questions
regarding the Array solution. I am able to make it work with the code below
(listed under "Code Works") -- is it possible to write this more efficiently
so that I don't need to specify what each cell individually needs? See the
code listed under "Would like to get code to work" below. Also, should I
specify a data type other than variant given that each cell in the array
contains no more than 10 letters, all text?

Code Works:

Sub copyheader()

Dim MyArray As Variant
Dim range1 As Range

MyArray = Sheets(2).[A1:L1]

With Sheets("Sheet3")
Set range1 = .Range("A1:A20")
For Each A In range1
If A.Value = "Need header" Then
A.Value = MyArray(1, 1)
A.Offset(0, 1).Value = MyArray(1, 2)
A.Offset(0, 2).Value = MyArray(1, 3)
A.Offset(0, 3).Value = MyArray(1, 4)
A.Offset(0, 3).Value = MyArray(1, 5)
A.Offset(0, 3).Value = MyArray(1, 6)
A.Offset(0, 3).Value = MyArray(1, 7)
A.Offset(0, 3).Value = MyArray(1, 8)
A.Offset(0, 3).Value = MyArray(1, 9)
A.Offset(0, 3).Value = MyArray(1, 10)
A.Offset(0, 3).Value = MyArray(1, 11)
A.Offset(0, 3).Value = MyArray(1, 12)
Else
End If
Next
End With

End Sub

Would like to get code to work:

Sub copyheader()

Dim MyArray As Variant
Dim range1 As Range

MyArray = Sheets(2).[A1:L1]

With Sheets("Sheet3")
Set range1 = .Range("A1:A20")
For Each A In range1
If A.Value = "Need header" Then
.Range(A, A.Offset(0, 11)) = .Range(MyArray(1, 1), MyArray(1, 12))
Else
End If
Next
End With

End Sub

--
Robert


"Incidental" wrote:

Hi Robert

Not sure if this will be any help to you or not but I thought I would
drop it in here just in case. from your code it looks like you are
just coping a range from one sheet and pasting it in the same format
in another sheet if the value in a specified range is "Need header" if
this is the case something like the code below should do what you
want.

Option Explicit
Dim MyCell, MyRng As Range

Private Sub CommandButton1_Click()

Set MyRng = Sheets(3).[A1:A50] 'Set your range

Sheets(1).[A1:L1].Copy 'Copy the required range

For Each MyCell In MyRng 'Move through each cell in MyRng

If MyCell.Value = "Need header" Then

MyCell.PasteSpecial (xlPasteAll)

End If

Next MyCell 'Iterate 1 cell

End Sub

However if you were planning on putting the values from A1:L1 into un-
contiguous cells then an array would be the way to go for me anyhow.
The code below should give you a better idea of how to do this.

Option Explicit
Dim MyArray As Variant

Private Sub CommandButton1_Click()

MyArray = Sheets(1).[A1:L1] 'Pass your range to an array

Sheets(3).Activate 'Move to the sheet you want

'Pick the cell you want the array value to be
'placed in and indicate where that value is in
'the array, it might help to visually think of
'the array in this case as a spreadsheet with
'values in the first row only indicated by (1,...
'then the column value being indicated by the
'second number here its marked with *'s (1,*1*).

[A1].Value = MyArray(1, 1)
[B2].Value = MyArray(1, 2)
[C3].Value = MyArray(1, 3)
[D4].Value = MyArray(1, 4)
[E5].Value = MyArray(1, 5)
[F6].Value = MyArray(1, 6)
[G7].Value = MyArray(1, 7)
[H8].Value = MyArray(1, 8)
[I9].Value = MyArray(1, 9)
[J10].Value = MyArray(1, 10)
[K11].Value = MyArray(1, 11)
[L12].Value = MyArray(1, 12)

End Sub

I hope that you find something of use in these examples.

Laterz

S