VBA & Macro Problems

How to Copy Threaded Comments to Another Excel Sheet Using VBA Macro

Maira Mehtab Sep 20, 2026 869 views

Question details

The user needs a VBA macro to extract threaded comments from a specific column (e.g., column F on a 'Test' sheet) and copy them to a 'Comments' sheet, including details like author, date, cell reference, comment text, and replies.

Product: Microsoft Excel
Device & OS: not provided
Scenario: Extracting and documenting threaded comments and replies into a structured table on a separate worksheet.
Observed behavior: Currently, threaded comments remain embedded in cells and need to be systematically exported, accounting for both threaded and legacy comment types without indexing errors.

Before you start

Before running the macro, ensure your workbook is saved as an Excel Macro-Enabled Workbook (.xlsm). Check if your worksheet contains modern Threaded Comments or Legacy Notes, as VBA handles them using different objects.

Solution 1Recommended

Extract Threaded Comments Using a VBA Macro

Use a VBA macro to iterate through threaded comments in a specific column and export their metadata and replies to a new sheet.

Modern Excel uses Threaded Comments, which are accessed via the CommentsThreaded object in VBA. To extract these, you must loop through this specific collection rather than standard legacy comments.

Open the VBA Editor

Press ALT + F11 on your keyboard to open the Visual Basic for Applications (VBA) Editor.

Insert a New Module

In the VBA Editor, click 'Insert' from the top menu and select 'Module' to create a blank script window.

Write the Extraction Code

Create a macro that loops through Worksheets("Test").CommentsThreaded. Add an If statement to check if the comment's parent column is F (e.g., If cmt.Parent.Column = 6 Then).

Extract Metadata and Replies

Inside the loop, write the properties such as cmt.Author.Name, cmt.Date, cmt.Parent.Address, and cmt.Text to your destination 'Comments' worksheet.

Fix Indexing Issues

Ensure your row tracking variable (e.g., i = i + 1) is placed exactly before the End If statement so each exported comment advances to the next output row without overwriting previous data.

Include Replies: To include replies, you will need to add a nested loop that iterates through the cmt.Replies collection for each threaded comment.

Solution 2

Run a Diagnostic Macro to Verify Comment Types

Determine whether your workbook contains threaded comments, legacy comments, or a mix of both by running a diagnostic counter macro.

WPS Spreadsheet Macro Support

Use VBA Macros in WPS Spreadsheets

WPS Office provides excellent support for VBA macros, allowing you to automate tasks such as extracting comments and formatting data effortlessly.

1. Open your workbook: Launch WPS Spreadsheets and open your Macro-Enabled Workbook containing the comments.
2. Access the Developer tab: Navigate to the Tools tab on the ribbon and click on 'Developer' to access the VBA environment.
3. Open Visual Basic Editor: Click 'Visual Basic Editor' to view, insert, or modify your macro code for extracting threaded comments.
4. Run the Macro: Press F5 or click the Run button to execute the script and view your exported comments on the destination sheet.

Fully compatible with Microsoft Excel (.xlsm) formatsBuilt-in Visual Basic Editor for writing and executing macrosFree and lightweight alternative to heavy office suitesSeamlessly handles cell formatting and data extraction

microsoft office alternative - wps office

Frequently Asked Questions

What is the difference between Threaded Comments and Legacy Comments in VBA?

In VBA, legacy comments (often called Notes) are accessed via the Range.Comment object. Modern threaded comments, which support user replies and statuses, are accessed via the Range.CommentThreaded object.

Why is my VBA macro overwriting the exported comments on the same row?

This usually happens due to an indexing issue. Ensure your row counter variable (such as i = i + 1) is correctly placed inside the active loop and right before the End If statement, so it properly advances to the next empty row for each new comment.

Can I extract the replies to a threaded comment using VBA?

Yes, you can extract replies by looping through the CommentThreaded.Replies collection for each parent threaded comment, allowing you to write the reply author, date, and text to your worksheet.

Excel & Data Problems