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.


--
Regards,
Tom Ogilvy


"DM" <not_correct@nowhere.com> wrote in message
news:uzk3jQA2FHA.3804@TK2MSFTNGP10.phx.gbl...
> 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
>
>

[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" <twogilvy@msn.com> wrote in message
news:OHvBGiA2FHA.980@TK2MSFTNGP14.phx.gbl...
> 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" <not_correct@nowhere.com> wrote in message
> news:uzk3jQA2FHA.3804@TK2MSFTNGP10.phx.gbl...
>> 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]

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

--
Regards,
Tom Ogilvy


"DM" <not_correct@nowhere.com> wrote in message
news:O1mxSoC2FHA.1256@TK2MSFTNGP09.phx.gbl...
> 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" <twogilvy@msn.com> wrote in message
> news:OHvBGiA2FHA.980@TK2MSFTNGP14.phx.gbl...
> > 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" <not_correct@nowhere.com> wrote in message
> > news:uzk3jQA2FHA.3804@TK2MSFTNGP10.phx.gbl...
> >> 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
> >>
> >>
> >
> >
>
>