VBA AutoFill in Excel — Fill a Series or a Formula Without the Destination Trap

TL;DR — source.AutoFill Destination, Type extends a pattern; it is not a copy. Two rules save you: the Destination must include the source — Range("A1").AutoFill Destination:=Range("A1:A10") (A1 is inside A1:A10); excluding the seed raises run-time error 1004. And the Type decides whether you get a series (xlFillSeries → 1, 2, 3) or a copy (xlFillCopy → 1, 1, 1); the default xlFillDefault guesses. If you only want to copy one cell down a column, Range("B2:B1000").FillDown is simpler than fighting AutoFill.

' Series: seed the first value(s), then extend
Range("A1").Value = 1
Range("A1").AutoFill Destination:=Range("A1:A10"), Type:=xlFillSeries  ' 1..10

' Formula down a column: relative refs adjust per row
Range("B2").Formula = "=A2*1.2"
Range("B2").AutoFill Destination:=Range("B2:B1000")

' Just copy the top cell down — no Destination gymnastics
Range("B2:B1000").FillDown

AutoFill is the code version of dragging the fill handle, and people expect it to behave like copy — so they are surprised when it turns 1 into 1, 2, 3, 4, and stumped when a snippet raises 1004 for no obvious reason. Both make sense once you hold the core idea: AutoFill continues a pattern from a seed across a destination that must contain that seed. Copy repeats; AutoFill generates. Get the destination and the type right and it is the cleanest way to lay down a series or push a formula down a column.

What you'll learn

• The mental model — AutoFill extends a pattern, it does not copy
• The destination rule — the source must be inside the destination, or 1004
• Type — series versus copy, and why the default guess is unreliable
• Filling a formula down so its references adjust per row
• When FillDown / FillRight (or an array) beat AutoFill

The mental model: AutoFill extends a pattern

Copy takes a value and repeats it. AutoFill takes a seed — the first cell or two — and continues whatever pattern it detects: a number becomes a run, 1, 2 becomes an arithmetic series, Jan becomes the months, a date becomes consecutive days. That is why it exists as something separate from Copy:

Range("A1").Value = 5
Range("A1").AutoFill Destination:=Range("A1:A5"), Type:=xlFillCopy    ' 5, 5, 5, 5, 5

Range("C1").Value = 5
Range("C1").AutoFill Destination:=Range("C1:C5"), Type:=xlFillSeries  ' 5, 6, 7, 8, 9

Same seed, same destination — different Type, completely different result. Once you see AutoFill as a generator that reads a seed and produces a pattern, the two rules below stop being arbitrary: the seed has to be part of what you fill, and you have to tell it which pattern you mean.

The destination rule: the source must be inside the destination

This is the AutoFill bug that stops every beginner: the Destination range must contain the source range. The source is the seed, and the seed is the first part of the fill.

' WRONG — destination excludes the seed -> run-time error 1004
Range("A1").AutoFill Destination:=Range("A2:A10")

' RIGHT — destination includes A1, the seed
Range("A1").AutoFill Destination:=Range("A1:A10")

Think of it as "fill A1 through A10," not "fill A1 into A2:A10." The seed row is where the pattern starts, so it lives at the top of the destination. Multi-cell seeds work the same way — seed A1:A2 with 1 and 2, then AutoFill Destination:=Range("A1:A100") and Excel reads the step from the two seed cells. Almost every "AutoFill throws 1004" question is a destination that forgot to include the source.

Type: series versus copy, and the unreliable default

Leave Type off and you get xlFillDefault, which lets Excel guess from the seed — a lone number copies, 1, 2 makes a series, Mon makes weekdays. Convenient in the UI, risky in code, because your intent is not written down and the guess can flip on you. Name the type:

Range("A1").Value = Date
Range("A1").AutoFill Destination:=Range("A1:A31"), Type:=xlFillMonths  ' month by month

The rule is simple: if the pattern matters, state it. Relying on xlFillDefault is how you get a copied 1, 1, 1 where you wanted 1, 2, 3, or a stepped series where you wanted a repeat.

Filling a formula down a column

The most useful everyday AutoFill is pushing a formula down a column. Fill a formula and its relative references adjust per row, exactly like dragging the fill handle:

Range("B2").Formula = "=A2*C2"
Range("B2").AutoFill Destination:=Range("B2:B1000")   ' B3 becomes =A3*C3, B4 =A4*C4, ...

xlFillDefault handles this correctly for formulas, so you rarely need to specify Type here. This is the idiomatic "apply this formula to the whole column," and it is why AutoFill earns its place even though FillDown exists — AutoFill can seed and extend a real series, where a plain copy cannot. If you would rather write the formula once and let it adjust without the fill-handle model, .FormulaR1C1 does the same job; see VBA Formula.

When FillDown, FillRight, or an array beat AutoFill

AutoFill is a pattern engine. When you are not generating a pattern — you just want the top cell repeated down or across — the simpler tools are clearer and skip the destination-includes-source ceremony:

Range("B2:B1000").FillDown        ' copy B2 down the whole range
Range("B2:Z2").FillRight          ' copy B2 across the whole row

FillDown and FillRight take no Type and no separate Destination — the range you call them on is the fill, top cell as the seed. For a pure numeric series with no formulas, writing a Variant array to the range in one assignment is faster still and avoids the worksheet operation entirely. My rule: reach for AutoFill only when you need Excel's pattern engine — a real series, weekdays, custom lists — and use FillDown / FillRight or an array for a plain copy. For big fills, turn off recalculation and screen updates first; see VBA Calculation and VBA ScreenUpdating.

How ExcelMaster helps

The AutoFill mistakes that waste time are predictable: the destination that excludes the seed and throws 1004, the missing Type that copied when you wanted a series, and the fight with AutoFill for a job that was really a one-line FillDown. None of them are hard once you know them — they are just easy to get wrong from memory.

ExcelMaster picks the right tool for the shape of the job. Ask it to "number these rows 1 to 500" and it seeds and extends a series with the destination including the seed; ask it to "copy this formula down to the last row" and it uses FillDown against the used range instead of a brittle hardcoded height. It states the Type so the intent is on the page, and it drops to a plain array write when you only need values. You describe the pattern; it lays it down without the 1004.

Frequently asked questions

How do I use AutoFill in VBA?

Call AutoFill on the seed range and pass a Destination that includes the seed: Range("A1").AutoFill Destination:=Range("A1:A10"), Type:=xlFillSeries. The seed is the first value or two, the Destination is the whole range to fill (seed included), and Type says whether to extend a series or copy. Set the seed cell first, then call AutoFill.

Why do I get run-time error 1004 with AutoFill?

Almost always because the Destination does not contain the source. Range("A1").AutoFill Destination:=Range("A2:A10") excludes the seed A1 and raises 1004; use Range("A1:A10") instead so the seed sits at the top of the destination. Read it as "fill A1 through A10," not "fill A1 into A2:A10."

What is the difference between AutoFill and FillDown in VBA?

FillDown copies the top cell of a range down the whole range — no Type, no separate Destination, just Range("B2:B1000").FillDown. AutoFill extends a pattern from a seed and needs a Destination that includes the seed. Use FillDown (or FillRight) for a plain copy, and AutoFill when you need a real series like 1, 2, 3 or the months.

How do I AutoFill a formula down a column in VBA?

Write the formula to the seed cell, then AutoFill it down: Range("B2").Formula = "=A2*C2" followed by Range("B2").AutoFill Destination:=Range("B2:B1000"). The relative references adjust per row, so B3 becomes =A3*C3. You can leave Type off for formulas. Range("B2:B1000").FillDown does the same thing more simply.

How do I fill a series of dates or months with AutoFill?

Seed the first date and pass a date Type: Range("A1").Value = Date then Range("A1").AutoFill Destination:=Range("A1:A31"), Type:=xlFillDays for consecutive days, or xlFillMonths / xlFillWeekdays / xlFillYears for those steps. Naming the type avoids relying on xlFillDefault to guess the pattern from the seed.

Tested in

Tested in: Excel 365 (Windows 11), VBA 7.1 — last verified 2026-09-01.

Related guides: VBA Cell Value · VBA Formula · VBA Copy Paste · VBA Range · VBA ScreenUpdating