Wednesday, March 6, 2013

Rectify VBA Error 1004

People who try to use the Macro functionality in the Microsoft Office Excel receive VBA Error 1004. Users receive it when they try to copy or paste the filtered data pro-grammatically using Macro.

VBA stands for Visual Basic for Applications. This is a Microsoft developed software used to provide interactive features in different programs.

This is the error message the users receive:

“Runtime Error 1004: Paste method of worksheet class failed.”
“Runtime Error 1004: Copy method of Range Class Failed.”


1.     Check the VBA Code for Mistakes
2.     Use On Error Statement
3.     Run System File Checker Tool
4.     Resolve Windows Registry Errors
5.     Reinstall Microsoft Excel

Check the VBA Code for Mistakes

You might have committed mistakes while writing the code. Coding mistakes often produce VBA Error 1004. Please minute check the entire coding, the commands specified. This shall resolve VBA Error 1004.

Use On Error Statement

The On Error Statement makes your application eligible to handle macro errors itself. Use it to fix VBA Error 1004.

Following is an example of On Error Statement:

Sub MyMacro()
On Error GoTo ErrorHandler
.   .   .
Exit Sub
.   .   .
Resume <or Exit Sub>
.   .   .
End Sub

Run System File Checker Tool

Error 1004 would be resolved by running System File Checker. It is an inbuilt feature in Windows that locates corrupt system files and replaces them with their original version. Follow the procedure given below to do so:

·         Close all active programs.
·         Click the Start button.
·         Click All Programs | Accessories. Right click Command Prompt, select Run as Administrator.
·         Enter username and password if system prompts you to do so.
·         Type following command at command prompt:
sfc /scannow
·         This command scans corrupt files and replaces them. During this operation you might require to enter Windows installation disc.

Resolve Windows Registry Errors

Windows Registry is fundamental component to run system applications smoothly. Microsoft Office Excel registry entries could be corrupt. You need to fix these registry errors to avoid VBA Error 1004. Here’s how to deal with your registry problem:

·         Find Registry Cleaner software
·         Download software and install it.
·         Run complete Registry Repair scan.
·         You will have a list of Registry errors.
·         Click Fix Errors button to resolve Windows Registry errors.

Reinstall Microsoft Excel

You need to uninstall and then reinstall Microsoft Excel program if all of the above mentioned solutions would not resolve VBA Error 1004. Follow the steps given below to uninstall and reinstall the program:

·         Click the Start button.
·         Click Control Panel | Programs | Program and Features | Uninstall a Program.
·         Right click Microsoft Office, select Uninstall/ Change. Select “Uninstall this program” and follow the wizard.
·         Now insert Microsoft Office CD/DVD
·         Double-click the setup file to install Microsoft Office Excel.

No comments:

Post a Comment

Note: Only a member of this blog may post a comment.