AutoFill Funtion Error

[DM]

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

 

[Tom Ogilvy]

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.

 

[DM]

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]

No, you only need to include the destination, at the same time, since the
ranges are contiguous, you can include the source range as well.

You can actually skip both and just do


newFormula = "='" & sheetName & "'!K$2"
Range(Cells(newRow, 4), Cells(newRow, 9)).Formula = _
newFormula