Resolve VBA Error 1004
Users receive VBA Error 1004 while copying and pasting filtered data in Excel Worksheet. Programmatic copy-pasting does not work. Following is the exact error message-
Runtime Error 1004: “Copy method of Range Class failed.”
Runtime Error 1004: “Paste method of Worksheet Class failed.”
VBA stands for Visual Basic for Applications, a program designed by Microsoft. Looking at the error message it is clear there is problem with the VBA and its Macro feature -- a feature in Microsoft Office Excel that performs input processes in the given sequence. These are the two components which are not working properly in your case.
Follow the given procedures to fix this problem:
- Modify Macro Code
- Reinstall VBA
- Unhide Hidden Sub-Sheet
- Repair Registry
- Disable Firewall
Modify Macro Code
Problems in the existing Macro Code can cause Microsoft Office Excel to show the above-mentioned error code. Modify your Macro Code. Insert following line in your existing code:
Range(Range("A" & ActiveCell.Row), Range("IV" & ActiveCell.Row).End(xlToLeft)).Select
Since the problem is with the VBA tool reinstall it. Program reinstallation prevents 1004 error. Here’s how:
- Click Start, select Control Panel.
- Click Programs | Uninstall a Program.
- Select “Microsoft Office 2010”. Click “Change” button.
- Select “Modify program installation” option, click Next.
- You’ll be shown a list of all installed features and components. Uncheck “Microsoft Visual Basic for Applications”, click Next.
- After completion, please reboot.
- Download the latest version online. Double click downloaded setup program and install it.
Unhide Hidden Sub-Sheet
One of your sub-sheets can be hidden. Unhide it either manually or programmatically to fix Error 1004.Manual
- Click the Format menu.
- Select Sheet | Unhide.
- Select a hidden sheet, click the Unhide button.
- Type following code: Sheets("Schedule X Phase").Visible=True (Replace “X” with appropriate sheet number)
- Execute the code.
Registry is a vital component of your system. Microsoft Office Excel registry entries contain several VBA and Macro settings. Corruption in the Registry causes this issue. Repair your Registry as shown below:
- Download RegInOut System Utilities.
- Install and run the program.
- Under Registry Repair section click the Start button.
- Scan and repair all errors. Reboot your machine.
Windows Firewall can cause interruptions executing your Macro Codes. Disable the firewall to fix this trouble.
- Click the Start button, select Control Panel.
- Click System and Security | Windows Firewall.
- Click “Turn Windows Firewall On or Off”. (Left hand side of the screen)
- Turn it off completely.
- Click OK.
- Total downloads:5231555
- Download Time:2sec on dsl, 2min on dial-up
- Compatibility:Windows 10, Windows 8, Windows 7, Windows Vista, Windows XP, Windows ME, Windows 2000 32Bit & 64Bit OS Systems
- Support: Yes