How to Duplicate an Excel Worksheet with VBA and ActiveX Controls
Question details
The user needs to copy an Excel worksheet containing VBA code and ActiveX controls without breaking the functionality or changing the control names.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Attempting to create a duplicate of a functional worksheet that utilizes ActiveX controls and VBA macros.
- Observed behavior
- Manually copying and pasting ranges causes the ActiveX control names to change and the associated VBA code to break, requiring the complete sheet structure to be copied instead.
Ensure your workbook is saved in a macro-enabled format (such as .xlsm) before duplicating sheets to prevent any loss of VBA code.
Use the Move or Copy Sheet Feature
Copying the entire worksheet tab instead of manually copying and pasting ranges preserves ActiveX controls and their associated VBA backend code.
When you manually copy cells containing ActiveX controls, the destination sheet treats them as new objects and assigns them new names (e.g., CommandButton2 instead of CommandButton1). This breaks the link to your existing VBA code. Using the built-in sheet copy function duplicates the underlying code module alongside the UI elements.
Locate the tab of the worksheet you want to duplicate at the bottom of the Excel window and right-click it.
Choose 'Move or Copy...' from the context menu that appears.
In the dialog box, check the box labeled 'Create a copy'. Choose where you want the duplicated sheet to be placed within the workbook, then click 'OK'.
Duplicate Macro-Enabled Worksheets Seamlessly in WPS Office
WPS Spreadsheet provides comprehensive support for VBA macros and ActiveX controls. You can easily duplicate complex worksheets without losing functionality, making it a powerful and free tool for your spreadsheet tasks.
- 1. Open Your Spreadsheet: Launch WPS Spreadsheet and open your macro-enabled workbook.
- 2. Access the Move or Copy Menu: Right-click the worksheet tab containing your ActiveX controls and select 'Move or Copy Sheet'.
- 3. Duplicate the Worksheet: Check the 'Create a copy' option, select the desired destination, and click 'OK' to duplicate the sheet with all macros intact.
Frequently Asked Questions
Why do my ActiveX controls stop working when I copy and paste data to a new sheet?
When you manually copy and paste ranges, the application only copies the objects, often renaming the controls automatically. Because your original VBA code explicitly targets the original control names, the macros fail. Using the 'Move or Copy' sheet feature ensures both the objects and their code structure are duplicated correctly with identical names.
How can I fix macro errors after successfully duplicating a worksheet?
If your macros fail on the duplicated sheet, the VBA code likely contains hardcoded references to the original sheet's name. Open the VBA Editor and review the code for references like `Sheets("OriginalSheetName")`. Update these references to use `ActiveSheet` or the name of the newly duplicated sheet.
Can I duplicate an Excel worksheet with macros to an entirely different workbook?
Yes. In the 'Move or Copy' dialog box, use the 'To book' dropdown menu. You can select another currently open workbook, or choose '(new book)' to create a brand new file containing the copied sheet, its ActiveX controls, and the associated VBA module.
