[Excel VBA] How to Extract and Reorder Specific Columns Using AdvancedFilter

2025年12月26日

Overview

By using the CopyToRange parameter of AdvancedFilter, you can extract only the necessary columns from your source data and copy them to another sheet in any order you like.

You can achieve this simply by arranging the “desired column names in the desired order” in the header row of the destination sheet. This allows for easy layout changes without needing Pivot Tables or Power Query.

Prerequisites

Item	Details
Excel Version	Microsoft 365 / 2016 or later
Source Data	“Sales” sheet, Range B3:F200 (includes headers)
Destination Sheet	“Export”
Columns to Copy	Date, Amount, Customer (3 columns)
Macro Location	Standard Module

Preparation Steps for the Destination Sheet

Enter the headers for the 3 columns mentioned above in range A1:C1 of the “Export” sheet in your desired order.

A1	B1	C1
Date	Amount	Customer

Leave the cells other than the headers blank. AdvancedFilter will recognize the headers in the destination sheet and write only the corresponding column data in that order.

VBA Sample Code

Sub CopySelectedFieldsInOrder()

    Dim rngSource As Range      ' Source data range
    Dim rngDest   As Range      ' Destination header cells
    
    '--- Set Ranges ---
    Set rngSource = Worksheets("Sales").Range("B3").CurrentRegion   ' B3:F200
    Set rngDest   = Worksheets("Export").Range("A1:C1")             ' Headers only
    
    '--- Copy using AdvancedFilter ---
    rngSource.AdvancedFilter _
        Action:=xlFilterCopy, _
        CriteriaRange:=Empty,    ' No conditions (all rows)
        CopyToRange:=rngDest, _
        Unique:=False            ' Change to True to remove duplicate rows

    MsgBox "Copied necessary columns in the desired order to the Export sheet.", vbInformation

End Sub

Code Explanation

Line	Explanation
7–8	Uses `CurrentRegion` to get the entire table from the “Sales” sheet, including the header row.
13	Omitting `CriteriaRange` (or setting it to Empty) targets all records without filtering.
14	Specifies the destination header range A1:C1 in `CopyToRange` to control the column order.
15	Changing `Unique:=False` to `True` allows you to exclude duplicate rows during the copy.

Application Points

Goal	How to Implement
Add a Serial Number column	After copying, add code like: `With Worksheets("Export").Range("D2").Resize(recordNum): .Formula = "=ROW()-1": End With`.
Filter by specific conditions	Set a condition range in `CriteriaRange`. You can freely combine OR/AND conditions.
Split sheets by month	Use a loop to shift the `CopyToRange` starting cell for each year/month to automatically generate sheets.

Frequently Asked Questions

Q: I misspelled a header cell, and the column was left blank.

A: The data will not be copied unless the header matches the source data exactly. Please use copy and paste to ensure accuracy.

Q: Will existing data in the destination be overwritten?

A: Yes, if there are existing cells, they will be overwritten; data is not appended below them. If you want to append data, please change rngDest to point to empty cells below the existing data.

Summary

By simply preparing the destination headers for AdvancedFilter‘s CopyToRange and setting the Unique parameter as needed, you can easily copy specific columns in your preferred order.

Please use this method when you want to quickly organize your data layout.

よかったらシェアしてね！