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
Source Range: Cells B4 and B5 contain the values and formats to be copied to the targeted cells. We set a variable named SourceRange to hold the values and formats of these two cells.
Target Range: Cells B4:B14 in column B that need to be filled. The target range also consists of the source range. We defined a variable named TargetRange to declare the target cells.
Autofill Type: xlFillCopy that will copy values and formats from the source range to the target range, repetition is allowed if necessary.
Worksheets: Name of the worksheet. In this example, the name of the worksheet is xlFillCopy (see the screenshot below)
Result: Cells B4 to B14 get auto filled with the values and formats of cells B4:B5. Repetition happens through the targeted cells.
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