Filter Table, Export specific column headers and column range to CSV, and save

DORSETRIGS

3 min read

·

02-09-2024

19

0

Share

Exporting Filtered Data to CSV: A Step-by-Step Guide

This article will guide you through exporting filtered data from a table in Excel to a separate CSV file, using a combination of VBA code and manual steps. We'll focus on filtering a table based on a specific criterion, selecting non-contiguous columns, and then exporting the selected data to a new CSV file. This technique is particularly helpful when you need to work with a subset of data extracted from a larger dataset.

The Problem:

You have a large table containing data in Excel, and you need to extract specific information based on a filter condition. You want to export this filtered data into a new CSV file, keeping only relevant columns in the output.

The Solution:

Here's a breakdown of the process, combining VBA code and manual steps:

1. Filter the Data

• VBA Code: The code below will filter the table "FieldOps" based on the criteria in column 1 (assuming "Generate" is in column 1).

Sub ExportNewReps()
' Define current and new file names
    CurrentFile = "FieldOps.xlsm"
    NewFile = "O365_FieldOps_Import.csv"

' Add new workbook
    Workbooks.Add

' Save New Workbook
    ActiveWorkbook.SaveAs ThisWorkbook.Path & "\" & NewFile, FileFormat:=xlCSV, CreateBackup:=False

' Filter and copy range to new workbook
    Windows(CurrentFile).Activate
    ActiveSheet.ListObjects("FieldOps").Range.AutoFilter Field:=1, Criteria1:= _
        "<>"
    ' ...rest of the code...
End Sub

• Explanation:
  • CurrentFile and NewFile variables store the names of the source and target files.
  • Workbooks.Add creates a new workbook to hold the exported data.
  • ActiveWorkbook.SaveAs saves the new workbook as a CSV file with the specified filename and path.
  • ActiveSheet.ListObjects("FieldOps").Range.AutoFilter filters the table "FieldOps" by column 1, excluding any rows where the value is empty (using <>).

2. Select Relevant Columns

• VBA Code: The code below selects the non-contiguous column ranges you need to export.

    ' ...previous code...
    Union(Range(Range("B1:E1"), Range("B1:E1").End(xlDown)), Range(Range("M1:N1"), Range("M1:N1").End(xlDown))).Select
    ' ...rest of the code...

• Explanation:
  • Union function combines two separate ranges into a single selection.
  • Range("B1:E1") selects the header row for columns B to E.
  • Range("B1:E1").End(xlDown) extends the selection down to the last row containing data in those columns.
  • The same logic is applied to columns M and N.

3. Copy and Paste Filtered Data

• VBA Code: The code below copies the selected data and pastes it into the new CSV workbook.

    ' ...previous code...
    Selection.Copy

    Windows(NewFile).Activate
    ActiveSheet.Paste
    ' ...rest of the code...

• Explanation:
  • Selection.Copy copies the selected data.
  • Windows(NewFile).Activate switches to the new CSV workbook.
  • ActiveSheet.Paste pastes the copied data into the active sheet.

4. Save and Close

• VBA Code: The code below saves the new CSV file and closes it.

    ' ...previous code...
    Columns("A:F").Select
    ActiveWorkbook.Save
    ActiveWorkbook.Close SaveChanges:=False
    ' ...rest of the code...

• Explanation:
  • ActiveWorkbook.Save saves the changes made to the new CSV file.
  • ActiveWorkbook.Close SaveChanges:=False closes the new CSV file without saving any further changes.

5. Clean Up

• VBA Code: The code below cleans up the original file.

    ' ...previous code...
    Windows(CurrentFile).Activate
    Range("A93").Select
    ActiveSheet.ListObjects("FieldOps").Range.AutoFilter Field:=1
    Range("A2").Select
End Sub

• Explanation:
  • ActiveSheet.ListObjects("FieldOps").Range.AutoFilter Field:=1 removes the filter applied to the original table.
  • The remaining code selects a specific cell in the original file, but you can remove this section if you don't need it.

Additional Notes:

• You can modify the column ranges in the Union function to select the specific columns you need to export.
• You can adjust the filter criteria in the AutoFilter method to suit your requirements.
• This code assumes that the original table is named "FieldOps" and that you have a workbook named "FieldOps.xlsm". You can modify these names as needed.

Important: Always make a copy of your original workbook before running any VBA code to avoid any accidental data loss. This code is a starting point, and you can customize it further based on your specific needs.

Example:

Let's say your table "FieldOps" has data about sales representatives, and you want to export only the information about representatives with a "Generate" value of "x". The code provided can be adapted to filter based on this criteria and select relevant columns like "Name", "Region", and "Sales Target" from the table.

By following these steps and adapting the provided VBA code, you can easily export filtered data from Excel tables to CSV files, making it easier to analyze and share your data.