Fix Excel VBA Macro Not Copying Rows Marked Yes
Question details
The user needs to fix an Excel VBA macro intended to copy inventory rows marked 'Yes' in column H to a 'discontinued' worksheet, but the macro currently does nothing.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Running a VBA script to automate the transfer of specific inventory data to another worksheet based on a column's criteria.
- Observed behavior
- The macro executes but nothing happens, likely because 'On Error Resume Next' is hiding runtime errors or macros are disabled after reopening the workbook.
Make sure you have a backup of your current workbook before editing VBA code. Check the Trust Center settings to ensure macros are enabled for your file.
Debug Hidden VBA Errors and Verify Macro Settings
Disable error suppression in your script to reveal hidden runtime errors, and ensure that Excel is permitted to run macros.
The command 'On Error Resume Next' forces VBA to ignore any errors and continue to the next line of code. While useful in some cases, it can hide typos in sheet names or broken logic, making it seem like the macro is doing nothing.
Press Alt + F11 in Excel to open the Visual Basic for Applications editor. Locate your workbook in the Project Explorer on the left and double-click the module containing your script.
Find the line that says 'On Error Resume Next'. Delete this line entirely or type a single apostrophe (') at the beginning of the line to comment it out.
Press F5 to run the macro again. If there is a reference error or failure in the script, Excel will now display a popup with the exact error message and highlight the problematic line.
Go to File > Options > Trust Center > Trust Center Settings > Macro Settings. Select 'Disable all macros with notification' or temporarily enable all macros to ensure execution isn't being blocked.
Optimize the VBA Code with Explicit Variables
Update the macro to use accurate worksheet references and dynamically calculate the last used rows to ensure data copies correctly.
Use WPS Spreadsheets to Manage Macros and VBA Scripts
WPS Office offers comprehensive support for Excel macro-enabled workbooks. You can easily open, debug, and run your VBA scripts with familiar developer tools in a lightweight environment.
- 1. Open Your Macro-Enabled Workbook: Launch WPS Spreadsheets and open your existing .xlsm file containing the inventory data.
- 2. Access Developer Tools: Navigate to the 'Developer' tab on the top ribbon. If you do not see it, go to Settings to enable the Developer options.
- 3. Edit the VBA Script: Click on 'Visual Basic' or 'Macros' to open the code editor. You can debug hidden errors or adjust worksheet variables directly.
- 4. Execute the Code: Run your modified script right inside WPS Spreadsheets to seamlessly copy the discontinued rows to the target sheet.
Frequently Asked Questions
Why does my Excel macro run without errors but do nothing?
This commonly happens if the macro uses the 'On Error Resume Next' command, which forces Excel to ignore errors and skip failing actions. It can also occur if the condition (like checking for 'Yes') doesn't exactly match the data due to trailing spaces or case-sensitivity.
How do I dynamically find the last used row in an Excel macro?
You can use the 'End(xlUp)' method to find the last row containing data. For example, 'Cells(Rows.Count, "A").End(xlUp).Row' calculates the last non-empty row in column A, ensuring you don't overwrite existing data.
Do I need to enable macros every time I open the workbook?
If you open the file from a standard folder, Excel may prompt you to enable macros for security reasons. To avoid this, you can move the workbook to a 'Trusted Location' defined in your Trust Center settings, but only do this for files you completely trust.
