VBA & Macro Problems

How to Fix Excel VBA PasteSpecial Error 1004 When Copying from Microsoft Project

Maira Mehtab Sep 27, 2026 869 views

Question details

Users encounter an intermittent runtime error 1004 in Excel VBA when executing a PasteSpecial command to copy data from Microsoft Project.

Product: Microsoft Excel, Microsoft Project
Device & OS: not provided
Scenario: Automating data transfer from Microsoft Project to Excel using VBA macros.
Observed behavior: The macro intermittently fails on the PasteSpecial line, throwing runtime error 1004 instead of pasting the values into the Excel worksheet.

Before you start

Ensure both Microsoft Excel and Microsoft Project are updated to their latest production versions, and identify the exact line of code triggering the error in your VBA editor.

Solution 1Recommended

Extract Data Directly via Project Objects (Recommended)

Bypassing the clipboard and directly reading Microsoft Project objects is much more reliable than using the copy and PasteSpecial method.

Relying on the Windows clipboard for automated tasks can cause intermittent failures like Error 1004 because the clipboard might not be ready when Excel attempts to paste. Directly querying the Project objects eliminates the dependency on the active view and clipboard state.

Open the VBA Editor

In Excel, press ALT + F11 to open the Visual Basic for Applications (VBA) Editor.

Enable Project Object Library

Navigate to 'Tools' > 'References' in the top menu and ensure the 'Microsoft Project Object Library' is checked.

Rewrite the Macro

Instantiate a Project application object (e.g., CreateObject("MSProject.Application")) within your code.

Map Values Directly

Loop through the MS Project tasks or resources and assign their properties directly to Excel cell values (for example, Range("A1").Value = pjApp.ActiveProject.Tasks(1).Name) instead of using the Copy and PasteSpecial xlPasteValues methods.

Debugging Tip: Keep a backup of your original code and comment out the old copy/paste lines until the direct object querying is fully tested and verified.

Solution 2

Isolate the Failing PasteSpecial Code

If you must use PasteSpecial, accurately diagnosing the failing line is the first step to mitigating timing or clipboard latency issues.

Free Microsoft Office alternative

Experience a Smoother Workflow with WPS Office

Tired of complex VBA troubleshooting and unexpected runtime errors? WPS Office offers a lightweight, highly compatible alternative to Microsoft Office. It supports advanced spreadsheet functions and macros, providing a seamless user experience without heavy resource demands.

1. Download the Installer: Visit the official WPS Office website and download the free installer package.
2. Install the Software: Run the setup file and follow the on-screen instructions to install the lightweight suite in just a few minutes.
3. Open Your Spreadsheets: Launch WPS Spreadsheet and seamlessly open your existing Excel files to continue your work with full format compatibility.

Fully compatible with Microsoft Excel (.xlsx, .xls) and macro-enabled files (.xlsm).Lightweight installation process and lightning-fast performance.Built-in advanced data processing tools that often reduce the need for complex VBA scripting.Familiar, easy-to-use tabbed interface for seamless migration.

microsoft office alternative - wps office

Frequently Asked Questions

Why does Error 1004 happen intermittently with PasteSpecial?

Error 1004 often occurs because the system clipboard is busy, empty, or hasn't finished receiving the copied data from Microsoft Project before Excel rapidly attempts to execute the paste command.

Does WPS Spreadsheet support VBA macros?

Yes, WPS Office provides built-in support for VBA macros in its advanced or enterprise versions, allowing you to run, edit, and debug most Excel macros seamlessly.

What does 'xlPasteValues' do in VBA?

'xlPasteValues' is a parameter used with the PasteSpecial method in Excel VBA to paste only the raw data (text or numbers) from the clipboard, stripping away any source formatting, borders, or formulas.

How can I avoid clipboard dependency in Excel VBA?

You can avoid the clipboard entirely by setting cell values directly equal to other objects or ranges (e.g., Range("B1").Value = Range("A1").Value), or by querying external application objects natively via their object libraries.

Excel & Data Problems