VBA & Macro Problems

How to Fix Excel VBA Not Copying a Row to a Tracker Workbook

Algirdas Jasaitis Nov 3, 2026 868 views

Question details

An Excel VBA macro fails to copy data from a training file to a separate tracker workbook when sent to another user.

How to Fix Excel VBA Not Copying a Row to a Tracker Workbook

Product: Microsoft Excel
Device & OS: not provided
Scenario: A user needs to run a VBA macro to copy specific training results (row 156, columns A through AB) from one workbook and seamlessly append them to the next available row in a separate tracker file.
Observed behavior: The macro works when both files are open locally on the original author's computer but fails for other users. The failure is caused by incorrect destination workbook references, inaccurate row calculations, and unreliable paste ranges.

Before you start

Ensure both the source workbook and the destination tracker workbook are accessible on the current computer, and verify that macros are enabled in your spreadsheet software's trust center.

Solution 1Recommended

Explicitly Reference Workbooks and Assign Values Directly

This is the most robust method for transferring data across workbooks in VBA, as it avoids clipboard errors and ensures data is directed to the exact intended worksheet.

Using the Copy and PasteSpecial methods across different workbooks can often lead to runtime errors, especially if the clipboard is interrupted. Directly assigning values between ranges is faster, cleaner, and less prone to errors when files are shared with multiple users.

Define explicit workbook and worksheet variables

In your VBA editor, set explicit references to both your source and destination sheets. For example: Set wsCopy = ThisWorkbook.Worksheets("TrainingSheet") and Set wsDest = Workbooks("trackerfile.xlsm").Worksheets("TrackerSheet").

Calculate the correct destination row

Determine the next empty row on the destination sheet by adding an offset to the last used row: lDestLastRow = wsDest.Cells(wsDest.Rows.Count, "A").End(xlUp).Offset(1).Row.

Assign values directly using Resize

Transfer the data without using the clipboard by making the destination range value equal to the source range value: wsDest.Range("A" & lDestLastRow).Resize(1, 28).Value = wsCopy.Range("A156").Resize(1, 28).Value.

Explicitly Reference Workbooks and Assign Values Directly

Code Optimization: Removing unnecessary variables like 'lCopyLastRow' when you only need to copy a static row (like row 156) will make your macro run faster and simplify your code.

Solution 2

Automatically Open the Tracker Workbook via VBA

If the macro is failing for other users, it may be because the required tracker workbook is not open or is located in a different directory on their machine.

Write and Execute VBA Macros Flawlessly in WPS Spreadsheet

WPS Office includes an advanced, user-friendly VBA editor that allows you to easily write, debug, and run complex macros across multiple workbooks. It perfectly mirrors the functionality you expect for spreadsheet automation while offering excellent compatibility.

1. Open your macro-enabled workbook: Launch WPS Spreadsheet and open your existing .xlsm file containing the data you wish to copy.
2. Access the Developer tab: Navigate to the top ribbon and click on the 'Developer' tab to reveal the macro tools.
3. Open the VBA Editor: Click the 'Visual Basic' button to open the integrated development environment.
4. Update your VBA code: Paste your explicitly referenced workbook code and direct value assignment logic into the appropriate module.
5. Run your macro: Click 'Run' or press F5 to execute the code and instantly copy your row to the tracker file.

Fully compatible with Microsoft Excel macro-enabled workbooks (.xlsm, .xlsb)Built-in, robust VBA editor for creating and debugging custom macrosEasily automates data transfer and row copying between multiple spreadsheetsFree, lightweight, and incredibly fast for everyday office tasks

microsoft office alternative - wps office

Frequently Asked Questions

Why does my Excel VBA macro work locally but fail for other users?

Macros often fail for other users if the code references hardcoded local file paths (like "C:\Users\YourName\Desktop") that don't exist on their computer. It can also fail if the target workbook is not currently open on their machine. Use shared network paths or ensure relative file paths are applied.

What is the benefit of assigning values directly instead of using Copy and PasteSpecial in VBA?

Direct value assignment (e.g., Range.Value = Range.Value) is considerably faster and bypasses the Windows clipboard entirely. This prevents your macro from crashing if the clipboard is cleared or accessed by another application while the script is running.

How do I dynamically find the next empty row in an Excel worksheet using VBA?

You can dynamically locate the next empty row in a specific column (such as Column A) by finding the last used row and offsetting it by one. The standard VBA syntax for this is: Cells(Rows.Count, "A").End(xlUp).Offset(1).Row.

Excel & Data Problems