How to Use VBA AutoFill in Excel (11 Examples)

Example 1 – Copy Information Down the Cells Using xlFillCopy

Dataset: In Column B, cells B4 and B5 contain numbers 1 and 2. The cells are also formatted with color and border.

Code: Insert code in the Visual Basic Editor.

Sub autofill()
Set SourceRange = Worksheets("xlFillCopy").Range("B4:B5")
Set TargetRange = Worksheets("xlFillCopy").Range("B4:B14")
SourceRange.autofill Destination:= TargetRange, Type:=xlFillCopy
End Sub

Read More: AutoFill Formula to Last Row with Excel VBA

Example 2 – Use of xlFillDays in Excel VBA to AutoFill Names of the Week

Dataset: In column B cells B3:B4 contain Sunday and Monday.

Code:

Sub autofill()
Set SourceRange = Worksheets("xlFillDays").Range("B3:B4")
Set TargetRange = Worksheets("xlFillDays").Range("B3:B11")
SourceRange.autofill Destination:= TargetRange, Type:=xlFillDays
End Sub

Source Range: Cells B3:B4 in column B

Target Range: Cells B3:B11 in column B

Worksheet: xlFillDays

Type: xlFillDays

Result: The 7 days of a week are displayed in the target ranges along with the formatting of the source range.

Read More: How to Fill Column in Excel with Same Value

Example 3 – Autofill Cells with the Default AutoFill Type of Excel VBA- xlFillDefault

Dataset: In column B cells B3:B4 contain numbers 1 and 3.

Code:

Sub autofill()
Set SourceRange = Worksheets("xlFillDefault").Range("B3:B5")
Set TargetRange = Worksheets("xlFillDefault").Range("B3:B11")
SourceRange.autofill Destination:= TargetRange, Type:=xlFillDefault
End Sub

Source Range: Cells B3:B4 in column B.

Target Range: Cells B3:B11 in column B.

Worksheet: xlFillDefault

Type: xlFillDefault

Result:  Excel determines the values and format pattern from the source range and fills the target range with a series of numbers starting from 1 with an interval of 1 among them that is 1, 3, 5, 7, 9, 11, 13

Example 4 – Copy Formats to Target Range Using xlFillFormats

Dataset: In column B cells B3:B5 are formatted with color (Green, Blue and White) and border.

Code:

Sub autofill ()
Set SourceRange = Worksheets("xlFillFormats").Range("B3:B5")
Set fillRange = Worksheets("xlFillFormats").Range("B3:B11")
SourceRange.autofill Destination:=fillRange, Type:=xlFillFormats
End Sub

Source Range: Cells B3:B5 in column B

Target Range: Cells B3:B11 in column B

Worksheet: xlFillFormats

Result: The formats from the source range is copied and auto-filled with repetition.

Read More: How to AutoFill Formula When Inserting Rows in Excel

Example 5 – Try Out xlFillMonths to AutoFill Months of the Year in Excel

Dataset: In column B cells B3:B5 contain months Jan, Feb and Mar.

Code:

Sub autofill ()
Set SourceRange = Worksheets("xlFillMonths").Range("B3:B5")
Set TargetRange = Worksheets("xlFillMonths").Range("B3:B14")
SourceRange.autofill Destination:= TargetRange, Type:=xlFillMonths
End Sub

Source Range: Cells B3:B5 in column B

Target Range: Cells B3:B14 in column B

Worksheet: xlFillMonths

Type: xlFillMonths

Result: The names of the 12 months of a year in the target range.

Example 6 – Get a Series Using xlFillSeries AutoFill Type

Dataset: The cells B3:B5 of column B contain three numbers 1, 4 and 7.

Code:

Sub autofill()
Set SourceRange = Worksheets("xlFillSeries").Range("B3:B5")
Set TargetRange = Worksheets("xlFillSeries").Range("B3:B10")
SourceRange.autofill Destination:= TargetRange, Type:=xlFillSeries
End Sub

Source Range: Cells B3:B5 in column B

Target Range: Cells B3:B10 in column B

Worksheet: xlFillSeries

Type: xlFillSeries

Result: Excel determines the values and format pattern from the source range and fills the targeted range with a series of numbers starting from 1 with an interval of 2 among them that is 1, 4, 7, 10, 13, 16, 19, 22.

Example 7 – AutoFill Values in Targeted Cells Using xlFillValues

Dataset: B3:B5 cells of column B store values 1, 2, and 3. These cells are formatted with color, text centering and border.

Code:

Sub autofill()
Set SourceRange = Worksheets("xlFillValues").Range("B3:B5")
Set TargetRange = Worksheets("xlFillValues").Range("B3:B10")
SourceRange.autofill Destination:= TargetRange, Type:=xlFillValues
End Sub

Source Range: cells B3:B5 in column B

Target Range: cells B3:B10 in column B

Worksheet: xlFillValues

Types: xlFillValues

Result: Running the code results in auto-filling only the values in the target cells. However, the formats of the source range weren’t carried to the target range.

Read More: Filling a Certain Number of Rows in Excel Automatically

Example 8 – Get a Range of Years Using Excel VBA with xlFillYears 

Dataset: B3:B4 cells of column B store two dates 1/1/2000 and 1/1/2001.

Code:

Sub autofill()
Set SourceRange = Worksheets("xlFillYears").Range("B3:B4")
Set TargetRange = Worksheets("xlFillYears").Range("B3:B10")
SourceRange.autofill Destination:= TargetRange, Type:=xlFillYears
End Sub

Source Range: cells B3:B4 in column B

Target Range: cells B3:B10 in column B

Worksheet: xlFillYears

Type: xlFillYears

Result: In the following cells of the source range, the year increases while the day and the month remains the same.

Example 9 – AutoFill Type- xlGrowthTrend 

Dataset: In column B, cells B3:B4 contain numbers 1 and 2.

Code:

Sub autofill()
Set SourceRange = Worksheets("xlGrowthTrend").Range("B3:B4")
Set TargetRange = Worksheets("xlGrowthTrend").Range("B3:B10")
SourceRange.autofill Destination:= TargetRange, Type:=xlGrowthTrend
End Sub

Source Range: Cells B3:B4 in column B

Target Range: Cells B3:B10 in column B

Worksheet: xlGrowthTrend

Types: xlGrowthTrend

Result:  1, 2 is extended as 4, 8, 16. Each number is a result of multiplying the previous number with some value (in this example: 2/1=2).

Example 10 – VBA AutoFill in Excel Using xlLinearTrend

Dataset: Cells B3 and B4 have two values 1 and 2.

Code:

Sub autofill()
Set SourceRange = Worksheets("xlLinearTrend").Range("B3:B4")
Set TargetRange = Worksheets("xlLinearTrend").Range("B3:B10")
SourceRange.autofill Destination: =TargetRange, Type:=xlLinearTrend
End Sub

Source Range: Cells B3:B4 in column B

Target Range: Cells B3:B10 in column B

Worksheet: xlLinearTrend

Type: xlLinearTrend

Result: 1, 2 is extended as 3, 4, and 5. Each number is a result of the addition of the previous number with some value (in this example: 2-1=1).

Example 11 – Convert Text to Columns Using xlFlashFill 

Dataset:  List of ICC World Cup Winners with the year of winning.

Code:

Sub autofill()
Set SourceRange = Worksheets("xlLinearTrend").Range("B3:B4")
Set fillRange = Worksheets("xlLinearTrend").Range("B3:B10")
SourceRange.autofill Destination: =fillRange, Type:=xlLinearTrend
End Sub

Source Range: Cells C3:B5 in column B

Target Range: Cells C3:C14 in column B

Worksheet: xlFlashFill

Type: xlFlashFill

Result: To extract the numerical part i.e., winning years, input the first 3 years manually to make Excel understand the pattern. After running the code rest of the cells get auto filled with winning years of the World Cup winners.

Things to Remember

▶ While writing multiple codes, keep the right sequence.

▶ Input the correct source range and target range to get desired result.

Download the Practice Workbook

Further Readings

• How to Autofill Dates in Excel 
  
• How to Autofill a Column in Excel
• How to Auto Populate from Another Worksheet in Excel
• How to Fill Down to Last Row with Data in Excel
• Excel Formulas to Fill Down Sequence Numbers Skip Hidden Rows