Tom, many thanks. I guess if I had read the help just a little more
carefully, I would have seen it :-)

Now that it works, I've noticed another annoyance. The formating of the
source cell is being copied so I will need to use the PasteSpecial method
instead, specifying the xlPasteFormulas type. Does this work the same way
wrt range? I realize I'll need to Copy before PasteSpecial.

DJM

"Tom Ogilvy" wrote in message
...
The destination range must include the source range. So

Sub AC()
sheetname = "ABCD"
newRow = 6
newFormula = "='" & sheetname & "'!K$2"
Cells(newRow, 4).Formula = newFormula
ActiveSheet.Cells(newRow, 4).Select
Selection.AutoFill Destination:=Range(Selection, _
Cells(newRow, 9)), Type:=xlFillDefault

End Sub


? cvErr(xlErrRef)
Error 2023

so this is the worksheet reference error.


--
Regards,
Tom Ogilvy


"DM" wrote in message
...
I'm not an experienced VBA programmer and need someone's help. The code
below comes from a recorded macro, with some ideas from the VBA help
files,
and I've attempted to modify it to be more generic and not associated
with
a
specific row or sheet.

After modifying a cell's formula to reflect a new sheet name, I'm
attempting
to copy it across several columns, as follows (newRow is Dim'ed as an
integer and is valid when I run the code):

newFormula = "='" & sheetName & "'!K$2"
Cells(newRow, 4).formula = newFormula
ActiveSheet.Cells(newRow, 4).Select
Selection.AutoFill Destination:=Range(Cells(newRow, 5), Cells(newRow,
9)), Type:=xlFillDefault

I get a run-time error 1004 - Autofill method of Range class failed. If
I
debug the code and hover the cursor over the various elements, I get
"Cells(newRow, 5)=Error 2023".

What am I doing incorrectly and where can I find the definitions of the
reported error codes? I've searched help and searched the MS website
without success.

TIA

DJM