VB copy formula and auto fill a range

[Blubber]

I am adding a formula residing in "Targeting Parameters" into
"DrListWorkCopy" using autofil into a whole column. When I compile the macro,
I got an error on the auto fill line saying" autofill method of Range class
failed.

Would appreciate some help.

My Codes:

Public Sub CopyFormulaDrListWorkCopy()
Dim Target1Wks As Worksheet, Source1Wks As Worksheet
Dim MBC As Range
Dim LR As Long, FillRange As Range

Set Target1Wks = Sheets("DrListWorkCopy")
Set Source1Wks = Sheets("Targeting Parameters")

With Source1Wks
Set MBC = Source1Wks.Range("D59")
End With

With Target1Wks
LR = Range("A65536").End(xlUp).Row
Set FillRange = Target1Wks.Range(Cells(3, 12), Cells(LR, 12))
MBC.AutoFill FillRange

End With
End Sub

 

[Harald Staff]

Instead of
MBC.AutoFill FillRange
try
FillRange.formular1c1 = MBC.formular1c1

HTH. Best wishes Harald

 

[OssieMac]

Hi,

If I understand correctly what you are trying to do then the problem is that
you can't start an autofill at a non adjacent cell to the start cell. You
need to copy the first cell and then autofill from that cell.

With Target1Wks
LR = Range("A65536").End(xlUp).Row
Target1Wks.Select
Set FillRange = Target1Wks.Range(Cells(3, 12), Cells(LR, 12))
MBC.Copy Destination:=FillRange.Cells(1, 1) 'Copy first cell
FillRange.Cells(1, 1).AutoFill FillRange 'Autofill from the first cell
End With

 

[Blubber]

That worked like a charm thanks. I now made slight changes to similarly
populate the other columns with formulas. I hit a snag getting the same error
message. I cant seem to see where went wrong... kindly help.

My new codes are as follows:

Public Sub CopyFormulaDrListWorkCopy()
Dim Target1Wks As Worksheet, Source1Wks As Worksheet
Dim MBC As Range, AT As Range, MNC As Range, Influ As Range, Third As Range
Dim FRng1 As Range, FRng2 As Range, FRng3 As Range, FRng4 As Range, Frng5 As
Range
Dim LR As Long
Set Target1Wks = Sheets("DrListWorkCopy")
Set Source1Wks = Sheets("Targeting Parameters")

With Source1Wks
Set MBC = .Range("D59")
Set AT = .Range("D60")
Set MNC = .Range("D61")
Set Influ = .Range("D62")
Set Third = .Range("D63")
End With

With Target1Wks
LR = Range("A65536").End(xlUp).Row

Set FRng1 = .Range("L3:L" & LR)
Set FRng2 = .Range("O3:O" & LR)
Set FRng3 = .Range("Q3:Q" & LR)
Set FRng4 = .Range("W3:W" & LR)
Set Frng5 = .Range("Z3:Z" & LR)

MBC.Copy Destination:=FRng1.Cells(1, 1)
FRng1.Cells(1, 1).AutoFill FillRange

AT.Copy Destination:=FRng2.Cells(1, 1)
FRng2.Cells(1, 1).AutoFill FillRange

MNC.Copy Destination:=FRng3.Cells(1, 1)
FRng3.Cells(1, 1).AutoFill FillRange

Influ.Copy Destination:=FRng4.Cells(1, 1)
FRng4.Cells(1, 1).AutoFill FillRange

Third.Copy Destination:=FRng1.Cells(1, 1)
Frng5.Cells(1, 1).AutoFill FillRange

End With
End Sub

 

[Blubber]

Oops, Found my typo mistake.... Forgot to change FillRange to FRng1.......

Thanks again.