VB copy formula and auto fill a range
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
"OssieMac" wrote:
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
--
Regards,
OssieMac
"Blubber" wrote:
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
|