How to Create a Dynamic Excel Template from Filtered Table Rows Using VBA
Question details
The user needs to dynamically copy visible rows from a filtered control table to a template worksheet and automatically update the print area based on the transferred data.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Generating dynamic template reports (like addendums) by transferring filtered data from a master table and adjusting the page layout for printing.
- Observed behavior
- Automating the data extraction and print area adjustment to avoid manually copying data and resetting the print range every time the filter criteria change.
Ensure your workbook is saved as an Excel Macro-Enabled Workbook (.xlsm) and safely back up your data before running or testing new VBA scripts.
Use a VBA Macro to Copy Filtered Data and Update the Print Area
Implement a customized VBA script to dynamically copy only the visible rows from a filtered table and automatically adjust the worksheet's print area based on the new data dimensions.
This method involves two primary macros: one to clear the old template data and copy the newly filtered visible rows, and a second to calculate the last populated row and redefine the page setup's print area.
Press Alt + F11 to open the Visual Basic for Applications (VBA) editor. Go to Insert > Module to create a new module for your macros.
Paste the following script to transfer visible cells: Sub UpdateAddendumTemplate() Dim DestRng As Range, SourceRng As Range Dim CTbl As ListObject With Sheets("CONTROL") Set CTbl = .ListObjects("Control") Set SourceRng = CTbl.ListColumns(1).DataBodyRange.Resize(CTbl.Range.Rows.Count - 1, 5).SpecialCells(xlCellTypeVisible) End With With Sheets("ADDENDUM TEMPLATE") Set DestRng = .Range("B42") DestRng.Resize(10000, 5).Clear End With SourceRng.Copy DestRng Call UpdatePrintArea MsgBox "Job Done!" End Sub
Below the first macro, paste the routine that updates the print area based on the last row: Sub UpdatePrintArea() Dim LastRow As Long With Sheets("ADDENDUM TEMPLATE") LastRow = .Range("B:F").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row .PageSetup.PrintArea = "" .PageSetup.PrintArea = .Range("B1:F" & LastRow).Address End With End Sub
Close the VBA editor. Filter your table on the 'CONTROL' sheet, then go to the Developer tab and click 'Macros'. Select 'UpdateAddendumTemplate' and click 'Run'. Your template and its print area will automatically update.
Alternative: Use the FILTER Function for Dynamic Data Transfer
If your version of Excel supports dynamic array formulas, you can extract filtered data without VBA, though you will need to handle the print area using dynamic named ranges.
Seamlessly Run VBA Macros in WPS Office
WPS Office Spreadsheet offers robust support for VBA macros and structured data tables, allowing you to run dynamic template scripts perfectly. Whether managing complex filtered lists or automating print setups, WPS provides a powerful environment to boost your productivity.
- 1. Open Your Macro Workbook: Launch WPS Spreadsheet and open your .xlsm file containing the dynamic template logic.
- 2. Access the Developer Tools: Navigate to the Developer tab on the ribbon to access the VBA Editor and Macro manager.
- 3. Execute Your Scripts: Run your customized macros to instantly filter tables, generate templates, and align your print areas.
Frequently Asked Questions
Why is my VBA macro copying hidden rows instead of only the filtered ones?
To ensure only visible, filtered rows are copied, you must append `.SpecialCells(xlCellTypeVisible)` to your source range in the VBA script before executing the `.Copy` command.
How do I dynamically set the print area without using VBA?
You can achieve this by creating a dynamic Named Range using the OFFSET and COUNTA functions via the Name Manager. Once defined, go to Page Setup and enter the exact Named Range into the 'Print area' box.
Can I trigger the template generation macro using a drop-down list?
Yes. You can use a Data Validation drop-down to select a criterion, and use the `Worksheet_Change` event in VBA to automatically filter the table and call your update macro whenever the drop-down cell is modified.
