VBA & Macro Problems

How to Use Excel VBA to Create Workbooks and Copy Filtered Data

Jason Miller Nov 3, 2026 870 views

Question details

The user needs to write a VBA macro that creates separate workbooks, copies only the visible filtered data from a source workbook, pastes it into the new workbooks, clears the filters, and copies specific columns.

How to Create Workbooks and Copy Filtered Data Using Excel VBA

Product: Excel
Device & OS: not provided
Scenario: Automating the extraction of filtered datasets to seamlessly separate data into newly generated workbook files without repetitive manual copying.
Observed behavior: Seeking the correct VBA syntax and methods, such as utilizing Workbooks.Add and SpecialCells, to perform the workflow efficiently without constantly selecting or activating windows.

Before you start

Before running any VBA macro, ensure you save a backup copy of your original workbook to prevent accidental data loss. You will also need to enable the Developer tab in your spreadsheet software to access the Visual Basic Editor.

Solution 1Recommended

Use Object Variables and SpecialCells in VBA

This method relies on defining Worksheet and Workbook object variables to avoid activating windows repeatedly, and uses the xlCellTypeVisible property to precisely copy only the filtered data.

By utilizing object variables like SourceSh and DestSh, the macro executes significantly faster and minimizes runtime errors compared to constantly using the ActiveSheet or Select methods.

The SpecialCells(xlCellTypeVisible) command is crucial for this task because it forces Excel to ignore hidden rows and only copy the data that is currently visible after applying your filters.

Open the VBA Editor

Open your source workbook containing the filtered data. Press Alt + F11 on your keyboard to launch the Visual Basic Editor.

Insert a New Module

In the VBA Editor, click on 'Insert' in the top menu bar, and then select 'Module' to create a blank script window.

Input the Macro Code

Copy and paste the following code into the module window: Sub Test() Dim SourceSh As Worksheet, DestSh As Worksheet Set SourceSh = ActiveSheet Application.ScreenUpdating = False Set DestSh = Workbooks.Add.Sheets(1) SourceSh.Cells.SpecialCells(xlCellTypeVisible).Copy DestSh.PasteSpecial Application.CutCopyMode = False SourceSh.ShowAllData Set DestSh = Workbooks.Add.Sheets(1) SourceSh.Columns("A:O").Copy DestSh.PasteSpecial Application.CutCopyMode = False SourceSh.Parent.Activate Application.ScreenUpdating = True End Sub

Adjust the Data Range

Locate the line reading SourceSh.Columns("A:O").Copy in the code. Change "A:O" to match the exact columns you wish to copy from your specific dataset.

Run the Macro

Close the VBA Editor. In your workbook, press Alt + F8 to open the Macro dialog box, select 'Test', and click 'Run' to execute the automation.

Use Object Variables and SpecialCells in VBA

Performance Optimization: The script uses Application.ScreenUpdating = False at the beginning and sets it back to True at the end. This prevents screen flickering and drastically reduces the macro's execution time when creating and pasting between multiple workbooks.

Efficient VBA Handling with WPS Office

Automate Data Processing with WPS Spreadsheet Macros

WPS Spreadsheet provides robust native support for VBA and macros, allowing you to automate repetitive tasks like filtering, copying, and creating new workbooks using the exact same code you would use in Microsoft Excel.

1. Access the Developer Tools: Open WPS Spreadsheet and navigate to the 'Developer' tab located on the top ribbon interface.
2. Open the VBA Editor: Click the 'WPS Macro' or 'Visual Basic' icon to launch the built-in VBA Editor.
3. Insert and Run Code: Insert a new Module, paste your filtering and copying VBA script, and press F5 to execute the automation seamlessly.

Full compatibility with Microsoft Excel VBA syntax and objectsLightweight application that executes macros swiftly and reliablyBuilt-in developer tools for writing, editing, and debugging your codeFree to download and use for all your daily office automation tasks

microsoft office alternative - wps office

Frequently Asked Questions

Why does my macro copy hidden rows instead of just the filtered data?

This happens if you do not explicitly command VBA to restrict its selection to visible cells. You must append .SpecialCells(xlCellTypeVisible) to your source range in the script to ensure hidden rows are excluded during the copy process.

How can I clear filters using VBA after copying the data?

You can clear filters by applying the ShowAllData method to your worksheet object (for example, SourceSh.ShowAllData). This command instantly removes all active filters on that specific sheet, making all rows visible again.

Does WPS Office support Excel VBA macros?

Yes, WPS Spreadsheet natively supports Excel VBA macros. You can run standard Excel macro scripts seamlessly within WPS Office, including complex operations like generating new workbooks, utilizing PasteSpecial, and manipulating objects.

Excel & Data Problems